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