I have this (awful) schema where the foreign keys are stored as a comma separated list in a column (FMID in table 2 below). Table 1 is a table with these keys in the second column (FMID). I need to add a new column to table 2, call it NEWFMID and in it store the PK from table 1 as shown. The problem is how to join for each FMID in the comma separated list in table 2 and save the new keys as a comma separated list in NEWFMID as well. Normalizing the db is not an option unfortunately, it would require a lot of change to 10 year old ASP application code. FMID can be null or empty in table 2 and null in table 1.Table 1 (int, nvarchar(100)) ID FMID1 L10052 L10083 L31854 L10135 L10186 NULL7 L10288 L10389 L104110 NULLtable 2 (int, varchar(100))PID FMID85 NULL283 L1005285 L1008287 L1005292 L1018300 L1018372 L1005,L1008374 L1041381 382 384 L1008,L1028387 L1008390 L1005,L1008,L1018,L1041table 2 with new column (int varchar(100), varchar(100))PID FMID NEWFMID85 NULL NULL283 L1005 1285 L1008 2287 L1005 1292 L1018 5300 L1018 5372 L1005,L1008 1,2374 L1041 9381 382 384 L1008,L1028 2,7387 L1008 2390 L1005,L1008,L1018,L1041 1,2,5,9
Thanks for any help you can provide.LW
Update query help
Posted - 2012-04-03 : 08:47:44
