Welcome to WebmasterWorld Guest from 220.127.116.11
Forum Moderators: open
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
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.