Solved

Update field with multiple values

Posted on 2008-06-25
7
402 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

738 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