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-08 : 11:21:58

I have to update "AllFiles" with adding "AFile" value where "Alvl" is 1. I used the following cursor, it works but my problems is i have millions records in this table and there are thausands same AID records so its taking more than an hour.
Is there any way to update quick?

Declare @tbl1 table
(ID INT,
AID INT,
Alvl INT,
AFile varchar(255),
AllFiles varchar(255)

)

INSERT INTO @tbl1
SELECT 1, 101,0,'Abc.html',NULL

INSERT INTO @tbl1
SELECT 2, 101,1,'Xyz.html',NULL

INSERT INTO @tbl1
SELECT 3, 101,1,'XXXX.html',NULL

INSERT INTO @tbl1
SELECT 4, 102,0,'DDD.pdf',NULL

INSERT INTO @tbl1
SELECT 5, 102,1,'mnop.doc',NULL

INSERT INTO @tbl1
SELECT 6, 102,1,'xtz.jpg',NULL

INSERT INTO @tbl1
SELECT 7, 103,0,'sksksk.zip',NULL

DECLARE @AID INT

DECLARE CUR_AID CURSOR FOR
SELECT AID FROM @tbl1

OPEN CUR_AID
FETCH NEXT FROM CUR_AID
INTO @AID
WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @AllFiles varchar(max),@AFile VARCHAR(255)
DECLARE CUR_AFile CURSOR FOR

SELECT AFile FROM @tbl1 WHERE AID=@AID And Alvl=1

SET @AllFiles = ''
OPEN CUR_AFile
FETCH NEXT FROM CUR_AFile
INTO @AFile
WHILE @@FETCH_STATUS = 0
BEGIN

IF @AllFiles = ''
SET @AllFiles = @AFile
ELSE
SET @AllFiles = @AllFiles + ';' + @AFile

FETCH NEXT FROM CUR_AFile
INTO @AFile
END
UPDATE @tbl1 SET AllFiles = @AllFiles WHERE AID = @AID AND Alvl = 0
CLOSE CUR_AFile
DEALLOCATE CUR_AFile

FETCH NEXT FROM CUR_AID
INTO @AID
END
CLOSE CUR_AID
DEALLOCATE CUR_AID

Select * from @tbl1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-08 : 11:25:08

you can use this method if you are using SQL 2005
concatenate records without UDF

or this if you are using SQL 2000
Rowset string concatenation: Which method is best ?

KH
[spoiler]Time is always against us[/spoiler]

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-06-08 : 11:26:38

I am using SQL 2005, I do not understand your code.......pls

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-08 : 11:33:04

[code]
select t.ID, t.AID, t.Alvl, t.AFile,
AllFiles = case when t.Alvl = 0 then
STUFF((SELECT TOP 100 PERCENT ',' + x.AFile
FROM @tbl1 AS x
WHERE x.AID = t.AID
and x.Alvl = 1
ORDER BY ',' + x.AFile FOR XML PATH('')), 1, 1, '')
end
from @tbl1 t
[/code]

KH
[spoiler]Time is always against us[/spoiler]

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-06-08 : 11:40:54

I have to update the records on "AllFiles" field, is this update script?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-08 : 11:42:54

quote:
Originally posted by rudba

I have to update the records on "AllFiles" field, is this update script?

No. this is a select query.

KH
[spoiler]Time is always against us[/spoiler]

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-06-08 : 13:03:28

Guys, anybody can help to update?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-08 : 19:36:31

[code]
~~select t.ID, t.AID, t.Alvl, t.AFile,~~
update t
set
AllFiles = case when t.Alvl = 0 then
STUFF((SELECT TOP 100 PERCENT ',' + x.AFile
FROM @tbl1 AS x
WHERE x.AID = t.AID
and x.Alvl = 1
ORDER BY ',' + x.AFile FOR XML PATH('')), 1, 1, '')
end
from @tbl1 t
[/code]

KH
[spoiler]Time is always against us[/spoiler]

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-06-10 : 10:09:10

Thanks, perfect job.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-10 : 10:11:49

to ORDER BY ID ?
just change the ORDER BY to