Improve company productivity with a Business Account.Sign Up

x
?
Solved

Update field with multiple values

Posted on 2008-06-25
7
Medium Priority
?
409 Views
Last Modified: 2013-11-05
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
0
Comment
Question by:chris_msl
  • 4
  • 3
7 Comments
 
LVL 20

Expert Comment

by:virmaior
ID: 21865548
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
 

Author Comment

by:chris_msl
ID: 21865782
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
 
LVL 20

Expert Comment

by:virmaior
ID: 21865879
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 20

Expert Comment

by:virmaior
ID: 21865893
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
 
LVL 20

Accepted Solution

by:
virmaior earned 1000 total points
ID: 21865897
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
 

Author Comment

by:chris_msl
ID: 21866187
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
 

Author Closing Comment

by:chris_msl
ID: 31470538
Excellent solutions as always.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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.

Join & Write a Comment

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question