Update field with multiple values

Hi,
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
tbl_activity.tbl_act_owner="-ABC-"
tbl_activity.tbl_act_owner="-DEF-"
tbl_activity.tbl_act_owner="-GHI-"

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

C
chris_mslAsked:
Who is Participating?
 
virmaiorCommented:
and the previous one...
sorry about that

UPDATE tbl_location SET tbl_location.loc_available_to = (SELECT loc_ref, GROUP_CONCAT(act_owner) AS AO FROM tbl_activity subq WHERE subq.act_owner LIKE '-%' AND subq.loc_ref = tbl_location.loc_id)
0
 
virmaiorCommented:
If you are using 4.1 or higher:

UPDATE tbl_location SET tbl_location.loc_available_to =
(SELECT GROUP_CONCAT(act_owner) FROM tbl_activity WHERE tbl_activity.act_owner LIKE '-%')


0
 
chris_mslAuthor Commented:
Hi Virmajor,

Thanks for the help. I have tried this, but it updates every location record with every activity owner. Some locations may only have 2 activies whilst other locations may have 10+ associated activities.

Have tried this, but there is a problem in the SQL, any ideas if it will work if I get the sql correct?
UPDATE tbl_location SET tbl_location.loc_available_to = subq.AO
(SELECT loc_ref, GROUP_CONCAT(act_owner) AS AO FROM tbl_activity WHERE tbl_activity.act_owner LIKE '-%') as subq
where subq.loc_ref = loc_id

Regards

C

0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
virmaiorCommented:
UPDATE tbl_location SET tbl_location.loc_available_to = (SELECT loc_ref, GROUP_CONCAT(act_owner) AS AO FROM tbl_activity WHERE tbl_activity.act_owner LIKE '-%')
WHERE subq.loc_ref = loc_id
0
 
virmaiorCommented:
err.. ignore that last comment

UPDATE tbl_location SET tbl_location.loc_available_to = (SELECT loc_ref, GROUP_CONCAT(act_owner) AS AO FROM tbl_activity WHERE tbl_activity.act_owner LIKE '-%' AND subq.loc_ref = tbl_location.loc_id)
0
 
chris_mslAuthor Commented:
Hi Virmaior,

Many thanks for your help, the following worked (just removed the loc_ref from select):
UPDATE tbl_location SET tbl_location.loc_available_to = ( SELECT GROUP_CONCAT( act_owner ) AS AO
FROM tbl_activity subq
WHERE subq.act_owner LIKE '-%'
AND subq.loc_ref = tbl_location.loc_id );

Excellent solution
0
 
chris_mslAuthor Commented:
Excellent solutions as always.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.