mySQL newbie(1 day) concatenate across result rows

Forum Moderators: open

Message Too Old, No Replies

mySQL newbie(1 day) concatenate across result rows

Update a field from multiple rows in another table

CDNQuilter

1:28 am on Dec 10, 2008 (gmt 0)

Inventory of storage units.
One owner can own all or part of one or more units.
Therefore, a unit can have one or more owners.

Currently, the owners file contains a list of units associated with that owner.
I need to update the units file with the owner_key and names of all owners.
So far, I have:

SELECT unit_no, units_owned,
TRIM( ',' FROM CONCAT_WS( ",",lastname,firstname,org_name)) AS Owner_Names
FROM units, owners
WHERE INSTR( units.unit_no, owners.units_owned )>0 0 LIMIT 0 , 300

gives me:

unit_no units_owned Owner_Names
100 100 Smith,John
200 200 Doe,John
200 200 Black,Jane
300 300 XYX Corporation

See that unit_no 200 is owned by both Doe,John and Black,Jane

I need to update units.owners with Doe,John;Black,Jane where units.unit_no=200

This seems very basic. I cannot find any examples, though. I need a WHILE but the WHILE examples I can find don't show how to incorporate this into a result set or an update
Thanks

phranque

1:58 am on Dec 10, 2008 (gmt 0)

what you really want is a separate table that associates owners and units.
you have a many-to-many relationship there that will be difficult to manage using a list column in both tables.

CDNQuilter

3:01 am on Dec 10, 2008 (gmt 0)

Well, right now they have an input form that allows them to select multiple unit numbers to put in the owners.units_owned field.

They also have an input form for the units table that allows them to put whatever they like in the units.owners field but, of course, they ended up out of sync so they gave up on it. Now they want the system to update this for them.
I guess I will just do the query as above in php and loop through the results to get the string of values and then create an update transaction.
Somehow, I thought that this should be do-able in SQL but SQL seems to require a different way of thinking than I am used to.

Thanks, anyway

CDNQuilter

6:52 am on Dec 10, 2008 (gmt 0)

I wrote a php routine that did the update.
If they keep the units_owned in the owners file accurate and run the routine I wrote regularly, it should be OK for now.
The system is a bit of a dog's breakfast in a few areas.
I will add your suggestion to their list of to-do's for when they reorganize the system.