Update field with multiple values
Posted on 2008-06-25
I have 2 tables with a 1 to many relationship. primary table (tbl_location) has a text field in it whcih I want to populate with all the child entries of another field eg.
tbl_location.loc_available_to = "-ABC-, -DEF-, -GHI-"
related child records
I have tried this, but it does not work. I assume its because the update is not committed for each row until the query is complete.
UPDATE tbl_location INNER JOIN tbl_activity ON tbl_location.loc_id = tbl_activity.loc_ref
SET tbl_location.loc_available_to = concat(loc_available_to,', ',act_owner)
WHERE (((tbl_activity.act_owner) Like '-%'));
Does anyone have any ideas as to how to get this working. I realise I could write a small routine in php for this, but ideally I need to do this in MySQL.
Thanks in advance