Yon Yon - 10 months ago 48
SQL Question

Update Table 1 memo field with values from table 2 in a one-to-many relationship

I have 2 tables I would like to update one column in table 1 with values from table 2 where id=id. However table 2 has many rows matching table 1 and all rows of table 2 would need to be updated to 1 row in table 1

Table_A

id | all_names |
---+-----------------+
1 |AB CD FG HI |
2 | |


** Table_B **

id | name |
---+-------+
1 | |
2 | Jon |
2 | Mike |


After the update Table 1 should look like

id | all_names |
---+-----------------+
1 |AB CD FG HI |
2 |Jon Mike |


I tried

update a
set a.all_names = TRIM(a.all_names) + b.name + ' '
from table_a a, table_b b
where a.id = b.id


All I end up getting is an empty all_names in table_a

Any idea?

Yon Yon
Answer Source

What I ended up doing

Declare @Crs cursor as select * from Table_B;   //Temp Table 
open @crs;
while fetch @crs do
update Table_A set all_names=ifnull(Table_B,'')+trim(@crs.name)+' ' where 
id=@Crs.id;
end while;
close @crs;

This uses the least of lines and is elegant

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download