Re: SQL Question: referencing two tables in update statement

Robert Christenson wrote:
>
> 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:
> >
> > snip
>
> 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.
>
> snip

If the proportion of rows to be updated in table_1 is small and the proportion of rows in table_2 used for updating is large, use:

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

                    FROM   table_2 ref2
                    WHERE  ref1.field_2 = ref2.field2)
WHERE ref1.field_1 IN
                   (SELECT field2
                    FROM   table_2)

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards"