Updating data with out CURSOR - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Updating data with out CURSOR

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-06-26 : 16:48:00

Hi guys, any body can help to customize following script to update past. The following script takes forever.

Declare @tbl2 table
(MID INT,
FName varchar(50)
)

INSERT INTO @tbl2
SELECT 1,'Abc'
INSERT INTO @tbl2
SELECT 2,'Xyz'
INSERT INTO @tbl2
SELECT 3,'Mnop'

Declare @tbl1 table
(ID INT,
MID INT,
DStatus varchar(1),
DupFName varchar(max)
)

INSERT INTO @tbl1
SELECT 1,1, 'A',NULL
INSERT INTO @tbl1
SELECT 2,2, 'A',NULL
INSERT INTO @tbl1
SELECT 3,3, 'A',NULL

DECLARE @Flag INT
SET @Flag = 4

WHILE (@Flag < 100000)
BEGIN
INSERT INTO @tbl1
SELECT @Flag,1, 'B',NULL
SET @Flag = @Flag + 1
END

WHILE (@Flag < 200000)
BEGIN
INSERT INTO @tbl1
SELECT @Flag,2, 'B',NULL
SET @Flag = @Flag + 1
END

WHILE (@Flag < 300000)
BEGIN
INSERT INTO @tbl1
SELECT @Flag,3, 'B',NULL
SET @Flag = @Flag + 1
END

--SELECT * from @tbl1

DECLARE @ID varchar(50)
DECLARE CURDup CURSOR FOR
SELECT ID FROM @tbl1
OPEN CURDup
FETCH NEXT FROM CURDup
INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @DupName varchar(max),@Cu VARCHAR(100)
DECLARE CUR_Dups CURSOR FOR

SELECT [FName]
FROM @tbl2 a
INNER JOIN (
SELECT ID,MID, DStatus
FROM @tbl1
) AS TC
ON TC.MID = a.MID WHERE TC.DStatus='B'
SET @DupName = ''
OPEN CUR_Dups
FETCH NEXT FROM CUR_Dups
INTO @Cu
WHILE @@FETCH_STATUS = 0
BEGIN

IF @DupName= ''
SET @DupName = @Cu
ELSE
SET @DupName = @DupName + ';' + @Cu

FETCH NEXT FROM CUR_Dups
INTO @Cu
END

CLOSE CUR_Dups
DEALLOCATE CUR_Dups

UPDATE @tbl1 SET DupFName = @DupName WHERE ID = @ID
FETCH NEXT FROM CUR_UpdateDup
INTO @ID
END
CLOSE CURDup
DEALLOCATE CURDup

SELECT * from @tbl1

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-26 : 17:06:08

I am looking for it - your sample is still running...

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 17:19:28

What you want is something like this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

E 12°55'05.63"
N 56°04'39.26"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-26 : 17:21:49

Wow Peso,
you can remember threads from 2007?

OK - I am a grandpa

No, you're never too old to Yak'n'Roll if you're too young to die.

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-06-26 : 17:53:48

Hello Peso,

Yes like this but i can not emplement as my requirment.

DECLARE @Sample TABLE (ID INT, Name VARCHAR(9))

INSERT @Sample
SELECT 3, 'Car' UNION ALL
SELECT 1, 'Lion' UNION ALL
SELECT 1, 'Rat' UNION ALL
SELECT 1, 'Mouse' UNION ALL
SELECT 2, 'Apple' UNION ALL
SELECT 2, 'Orange'

-- Show the expected output
SELECT DISTINCT s1.ID,
STUFF(
(
SELECT CASE
WHEN Item = 1 AND Items > 1 THEN ' and '
ELSE ', '
END + s2.Name
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY s2.ID ORDER BY s2.Name DESC) AS Item,
COUNT(*) OVER (PARTITION BY s2.ID) AS Items,
s2.Name
FROM @Sample AS s2
WHERE s2.ID = s1.ID
) AS s2
ORDER BY Item DESC
FOR XML PATH('')
), 1, 2, '') AS Items
FROM @Sample AS s1
ORDER BY s1.ID

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-06-26 : 17:55:26

I tried to emplement as update on my case so could not get success.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-27 : 02:55:08

What is your requirement?
What is your expected output of the sample data posted 06/26/2009 : 17:53:48 ?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-27 : 02:59:11

[code]DECLARE @Sample TABLE
(
ID INT,
Name VARCHAR(9)
)

INSERT @Sample
SELECT 3, 'Car' UNION ALL
SELECT 1, 'Lion' UNION ALL
SELECT 1, 'Rat' UNION ALL
SELECT 1, 'Mouse' UNION ALL
SELECT 2, 'Apple' UNION ALL
SELECT 2, 'Orange'

-- Show the expected output
SELECT i.ID,
STUFF(p.z, 1, 1, '') AS Names
FROM (
SELECT ID
FROM @Sample
GROUP BY ID
) AS i
CROSS APPLY (
SELECT ';' + s.Name
FROM @Sample AS s
WHERE s.ID = i.ID
ORDER BY s.Name
FOR XML PATH('')
) AS p(z)
ORDER BY i.ID[/code]

E 12°55'05.63"
N 56°04'39.26"

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-06-27 : 10:38:20

Can u update these names on new field?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-27 : 11:02:10

Of course!
The output is a resultset made of columns and records.

E 12°55'05.63"
N 56°04'39.26"

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-06-29 : 20:06:39

How do i update "DupFName" field with out using cursor
Declare @tbl2 table
(MID INT,
FName varchar(50)
)

INSERT INTO @tbl2
SELECT 1,'Abc'
INSERT INTO @tbl2
SELECT 2,'Xyz'
INSERT INTO @tbl2
SELECT 3,'Mnop'

Declare @tbl1 table
(ID INT,
MID INT,
DStatus varchar(1),
DupFName varchar(max)
)

INSERT INTO @tbl1
SELECT 1,1, 'A',NULL
INSERT INTO @tbl1
SELECT 2,2, 'A',NULL
INSERT INTO @tbl1
SELECT 3,3, 'A',NULL

DECLARE @Flag INT
SET @Flag = 4

WHILE (@Flag < 100000)
BEGIN
INSERT INTO @tbl1
SELECT @Flag,1, 'B',NULL
SET @Flag = @Flag + 1
END

WHILE (@Flag < 200000)
BEGIN
INSERT INTO @tbl1
SELECT @Flag,2, 'B',NULL
SET @Flag = @Flag + 1
END

WHILE (@Flag < 300000)
BEGIN
INSERT INTO @tbl1
SELECT @Flag,3, 'B',NULL
SET @Flag = @Flag + 1
END

--SELECT * from @tbl1

DECLARE @ID varchar(50)
DECLARE CURDup CURSOR FOR
SELECT ID FROM @tbl1
OPEN CURDup
FETCH NEXT FROM CURDup
INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @DupName varchar(max),@Cu VARCHAR(100)
DECLARE CUR_Dups CURSOR FOR

SELECT [FName]
FROM @tbl2 a
INNER JOIN (
SELECT ID,MID, DStatus
FROM @tbl1
) AS TC
ON TC.MID = a.MID WHERE TC.DStatus='B'
SET @DupName = ''
OPEN CUR_Dups
FETCH NEXT FROM CUR_Dups
INTO @Cu
WHILE @@FETCH_STATUS = 0
BEGIN

IF @DupName= ''
SET @DupName = @Cu
ELSE
SET @DupName = @DupName + ';' + @Cu

FETCH NEXT FROM CUR_Dups
INTO @Cu
END

CLOSE CUR_Dups
DEALLOCATE CUR_Dups

UPDATE @tbl1 SET DupFName = @DupName WHERE ID = @ID
FETCH NEXT FROM CUR_UpdateDup
INTO @ID
END
CLOSE CURDup
DEALLOCATE CURDup

SELECT * from @tbl1

the out put of DupFName will be like:
Abc;Xyz;Mnop

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-06-30 : 09:41:58

Anybody got idea?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-30 : 14:07:23

Why doesn't the FOR XML PATH approach work for you, as suggested 06/27/2009 : 02:59:11 ?

N 56°04'39.26"
E 12°55'05.63"

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-06-30 : 16:00:19

Coz. i have two tables and it have to be update so i could not get...

X002548
Not Just a Number

15586 Posts

Posted - 2009-06-30 : 16:34:53

what am I missing?



/*

the out put of DupFName will be like:
Abc;Xyz;Mnop

*/
DECLARE @DupFName varchar(max)
DECLARE @tbl2 table (MID INT, FName varchar(50))

INSERT INTO @tbl2 (MID, FName)
SELECT 1,'Abc' UNION ALL
SELECT 2,'Xyz' UNION ALL
SELECT 3,'Mnop'

SELECT @DupFName = COALESCE(@DupFName+';'+FName,FName)
  FROM @tbl2

SELECT @DupFName

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources