Solved

Update field with multiple values

Posted on 2008-06-25
7
399 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Suggested Solutions

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…
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

867 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now