Re: SQL Question: referencing two tables in update statement

Stuart Chaulk wrote:
>
> Hi there,
>
> Quick SQL guestion:
>
> How do I reference two tables in an SQL statement. What I need is
> something like this, but not sure about the syntax:
>
> update table_1 ref1, table_2, ref2
> set ref1.field_1 = ref2.field_1
> where ref1.field_2 = ref2.field2;
>
> Any help would be VERY MUCH appreciated.
>
> Thanks,
>
> Stuart
>
> --
> Stuart Chaulk - Project Logistician
> Community Health Resource Project
> University of British Columbia | Centre for Excellence in HIV/AIDS
> schaulk_at_hivnet.ubc.ca | http://cfeweb.hivnet.ubc.ca/chrp/

UPDATE table_1 ref1
SET ref1.field_1 = (SELECT ref2.field_1

			FROM table_2 ref2
			WHERE ref1.field_2 = ref2.field2);

This will update all records in ref1, setting field_1 = NULL if there is not a match in ref2. Use the following to update only records where there is a match in ref2.

UPDATE table_1 ref1
SET ref1.field_1 = (SELECT ref2.field_1

			FROM table_2 ref2
			WHERE ref1.field_2 = ref2.field2)
WHERE EXISTS (SELECT 'x' FROM table_2 ref3
		WHERE ref1.field_2 = ref3.field2);

There may be a more elegant way to implement the 'WHERE EXISTS' clause, but I didn't see it.

Hope this helps!

-- 
"Came for the party, left on the run"

Robert Christenson
Gazette Technologies
robertoc#spam~be~gone#_at_fyiowa.infi.net