开发者

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, '') 
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜