sql update query with concatation on value? Is this possible

Question

Post reply

sql update query with concatation on value? Is this possible

bugg

SSCertifiable

Points: 7567
More actions
October 1, 2014 at 8:19 am

#312022

I'm trying to concatanate values on a table update from another table.
DECLARE @tbl_to TABLE(col1 INT, col2 nvarchar(100))
INSERT INTO @tbl_to(col1,col2)
VALUES ( 1,'' )
INSERT INTO @tbl_to(col1,col2)
VALUES ( 2,'' )
INSERT INTO @tbl_to(col1,col2)
VALUES ( 3,'' )
INSERT INTO @tbl_to(col1,col2)
VALUES ( 4,'' )
INSERT INTO @tbl_to(col1,col2)
VALUES ( 5,'' )
DECLARE @tbl_from TABLE(col3 INT, col4 nvarchar(100))
INSERT INTO @tbl_from
SELECT 1, 'a'
UNION ALL
SELECT 2, 'a'
UNION ALL
SELECT 2, 'b'
UNION ALL
SELECT 3, 'x'
UNION ALL
SELECT 4, 'y'
UNION ALL
SELECT 5, 'd'
UNION ALL
SELECT 5, 'e'
UNION ALL
SELECT 5, 'f'
UPDATE @tbl_to
SET col2 = col2 + col4 + ','
FROM @tbl_from WHERE [@tbl_to].col1 = [@tbl_from].col3
SELECT * FROM @tbl_to
Is there any way to do this without using XMLPATH and the STUFF() function. I've tried this and weirdly the query is extremely slow on an update, select is fine though ?
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

You might be experiencing a problem described by Jeff Moden in the following article:

http://www.sqlservercentral.com/articles/Performance+Tuning/62278/

I'm not sure how you tried to do the update with XML PATH, but it might not be the cause of the problem.

UPDATE t

SET col2 = col2 + STUFF( (SELECT ',' + f.col4

FROM @tbl_from f

WHERE t.col1 = f.col3

FOR XML PATH('')), 1, 1, '')

FROM @tbl_to t

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

bugg SSCertifiable Points: 7567 More actions · Answer 2

Luis Cazares (10/1/2014)
You might be experiencing a problem described by Jeff Moden in the following article:
http://www.sqlservercentral.com/articles/Performance+Tuning/62278/
I'm not sure how you tried to do the update with XML PATH, but it might not be the cause of the problem.
UPDATE t
SET col2 = col2 + STUFF( (SELECT ',' + f.col4
FROM @tbl_from f
WHERE t.col1 = f.col3
FOR XML PATH('')), 1, 1, '')
FROM @tbl_to t

Thanks for putting me on the right track, that article was extremely useful!

One thing, I've added a 'where exists' to only update relevant results..