Solved

Update field with multiple values

Posted on 2008-06-25
7
401 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 250 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

821 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