Updating table based on 2 tables with RowID is as commonality
I have a table like this:
RowID; ListDescription1; ListNormalisedDescription1;
1 XXXX YYYY NULL
2 ZZZZZ NULL
I made a complex transformation/normalisation (removing spaces, replacing space and split) and manage to make the same data turning into:
RowID; NormalisedItemDescrption1;
1 XXXX
1 YYYY
2 ZZZZZ
AS you can see the commonality between these 2 tables is RowID.
I want to update ListNorma开发者_C百科lisedDescription1 based on the table so become:
RowID; ListDescription1; ListNormalisedDescription1;
1 XXXX YYYY XXXX;YYYY
2 ZZZZZ ZZZZZ
Please note that the delimiter is in ';'
I am trying to avoid cursor if it's possible.
Thanks
Assuming SQL Server 2005+, use:
UPDATE table
SET ListDescription1 = STUFF(ISNULL(SELECT ' ' + x.NormalisedItemDescrption1
FROM NORMALIZED_TABLE x
WHERE x.rowid = rowid
GROUP BY x.NormalisedItemDescrption1
FOR XML PATH ('')), ''), 1, 2, ''),
ListNormalisedDescription1 = STUFF(ISNULL(SELECT ';' + x.NormalisedItemDescrption1
FROM NORMALIZED_TABLE x
WHERE x.rowid = rowid
GROUP BY x.NormalisedItemDescrption1
FOR XML PATH ('')), ''), 1, 2, '')
精彩评论