Re: Help with SQL - Update between tables

On 26 jun, 16:20, Problematic coder <gnews..._at_gmail.com> wrote:
> Here is what I am trying to achieve in sudo code:
>
> update table1 set
> table1.fname = table2.fname,
> table1.lname = table2.lname
> where table1.emplid = table2.emplid;
>
> Now clearly this is wrong since it gives me an error, the error I get
> is "invalid identifier - table2.emplid"
>
> In English what I want to do is set the fname and lname of table1 to
> that of table2 where the emplid's match
>
> The column names given above are the actual column names, so I am
> fairly sure these are not reserved words which i know can cause this
> error, it must be my awful SQL
>
> Thanks for any assistance

Disclaimer: untested

update table1 A set
(fname, lname) = (select B.fname, B.lname   from table2 B
  where B.emplid = A.emplid)
where exists
(select * from table2 C where C.emplid = A.emplid);

If you omit the "where exists" part, records that exist only in table1 and not in table2 will be updated to NULL. Received on Tue Jun 26 2007 - 14:31:02 CDT