three table join

[krisc13]

I have a customer id in a customer table that I need to insert into the proper rows (update) of an activity table. The tables connect through a third table the person table. The following select statement seems to work...

SELECT `customer`.`cust_id`
FROM `f_agent`.`customer`
LEFT JOIN `f_agent`.`person`
ON `customer`.`cust_person_id` = `person`.`person_id`
LEFT JOIN `f_agent`.`activity`
ON `person`.`prev_cust_id` = `activity`.`prev_cust_id`
WHERE `activity`.`prev_cust_id` != "0"

I want to write an update statement that will insert the cust_id into activity.activity_with_id based on the select statement above. I tried

UPDATE `f_agent`.`activity`
SET `activity`.`activity_with_id` =
(SELECT `customer`.`cust_id`
FROM `f_agent`.`customer`
LEFT JOIN `f_agent`.`person`
ON `customer`.`cust_person_id` = `person`.`person_id`
LEFT JOIN `f_agent`.`activity`
ON `person`.`prev_cust_id` = `activity`.`prev_cust_id`
WHERE `activity`.`prev_cust_id` != "0")

however it didn't work. Any suggestions would be appreciated...Thanks.

 

[feherke]

Hi

That way all records will be set to cust_id. Excepting the case when the sub-[tt]select[/tt] returns more then one tuple and the command fails.

So you probably need to put a sub-[tt]select[/tt] in the [tt]update[/tt] command's [tt]where[/tt] clause and make sure the sub-[tt]select[/tt]s returns only one tuple.

Feherke.

http://rootshell.be/~feherke/