three table join

[krisc13]

I am trying to insert customer ids into an activity table that relates to customers thru the person table. It's very strange has to do with the data I'm trying to migrate from one database to another. The following select statement works or seems to...

SELECT `customer`.`cus t_id`
FROM `f_agent`.`cust omer`
LEFT JOIN `f_agent`.`pers on`
ON `customer`.`cus t_person_id` = `person`.`perso n_id`
LEFT JOIN `f_agent`.`acti vity`
ON `person`.`prev_ cust_id` = `activity`.`pre v_cust_id`
WHERE `activity`.`pre v_cust_id` != "0"

Now I want to update my activity table with the cust_ids. I tried

UPDATE `f_agent`.`acti vity`
SET `activity`.`act ivity_with_id` =
(SELECT `customer`.`cus t_id`
FROM `f_agent`.`cust omer`
LEFT JOIN `f_agent`.`pers on`
ON `customer`.`cus t_person_id` = `person`.`perso n_id`
LEFT JOIN `f_agent`.`acti vity`
ON `person`.`prev_ cust_id` = `activity`.`pre v_cust_id`
WHERE `activity`.`pre v_cust_id` != "0")

Which obviously doesn't work. I need to loop thru my activities and set activity_with_i d = cust_id based on the joins above. Any suggestions?

[xpcer]

I am trying to insert customer ids into an activity table that relates to customers thru the person table. It's very strange has to do with the data I'm trying to migrate from one database to another. The following select statement works or seems to...

SELECT `customer`.`cus t_id`
FROM `f_agent`.`cust omer`
LEFT JOIN `f_agent`.`pers on`
ON `customer`.`cus t_person_id` = `person`.`perso n_id`
LEFT JOIN `f_agent`.`acti vity`
ON `person`.`prev_ cust_id` = `activity`.`pre v_cust_id`
WHERE `activity`.`pre v_cust_id` != "0"

Now I want to update my activity table with the cust_ids. I tried

UPDATE `f_agent`.`acti vity`
SET `activity`.`act ivity_with_id` =
(SELECT `customer`.`cus t_id`
FROM `f_agent`.`cust omer`
LEFT JOIN `f_agent`.`pers on`
ON `customer`.`cus t_person_id` = `person`.`perso n_id`
LEFT JOIN `f_agent`.`acti vity`
ON `person`.`prev_ cust_id` = `activity`.`pre v_cust_id`
WHERE `activity`.`pre v_cust_id` != "0")

Which obviously doesn't work. I need to loop thru my activities and set activity_with_i d = cust_id based on the joins above. Any suggestions?

you can't do like that, because, this subquery in the update query may be return a multiple value, then, the system can't to detect which value will be updated to the old data.

in my suggestion, you can create an aplication that can be use to loop the result of the 'select' statement, and then update the data.