Need subquery in SET clause of UPDATE 1

[mhoyt]

I have a blank field in one table that I want to update based on information in another table. The tables are related and the subquery in my SET = clause returns the right information, but I get the error that my subquery cannot return more than one value.

How can I relate the list of returned values to the original table so I can update it?

UPDATE TABLE1
SET LASID = (SELECT TABLE2.STD_NUMBER 
			FROM TABLE2 JOIN TABLE3 
				ON TABLE2.ID = TABLE3.ID
			JOIN TABLE1 
				ON TABLE1.ID = TABLE3.ID)

TIA

 

[gmmastros]

Try something like this...

UPDATE TABLE1
SET    TABLE1.LASID = TABLE2.STD_NUMBER
FROM   TABLE2 
       JOIN TABLE3 
         ON TABLE2.ID = TABLE3.ID
       JOIN TABLE1 
         ON TABLE1.ID = TABLE3.ID)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Oh, I need to suggest that you backup your database first. If the query runs and it updates the wrong data, you could end up with a real mess on your hands.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mhoyt]

Brilliant! And so prompt. Thank you so much!
(And not bad advice about backing up first either!)