Re: Update table from another table

I found that the following statement can run in most of the RDBMS but I don't know if it is the correct and standard way to write the update SQL:

update employee

   set fname = (SELECT E1.fname FROM employee_bk as E1

                WHERE employee.emp_id = E1.emp_id)
     , lname = (SELECT E1.lname FROM employee_bk  E1 
                WHERE employee.emp_id = E1.emp_id)
 where exists  (SELECT * FROM employee_bk  E1 
                WHERE employee.emp_id = E1.emp_id);

Wed, 28 Mar 2001 10:47:52 +0300 Deon <dvrmail_at_yahoo.com> wrote:
> In SQL you are not allowed to alias the table being updated, therefore you
> would want to use Germano's syntax or this example which is a bit closer to
> what you were aiming at :
>
> update employee
> set fname = b.fname,
> lname = b.lname
> from employee_bk b
> where employee.emp_id = b.emp_id
>
> Deon
>
> "Michel Roberge" <mrobergeNO_at_SPAMstarlims.com> wrote in message
> news:o%_v6.10162$Uy.46538849_at_news1.tor.primus.ca...

>> In fact it should be written like this (at least, its the syntax I'd use)
>>
>> update employee a
>> set a.fname = b.fname,
>>      a.lname = b.lname
>> from employee_bk b
>> where a.emp_id = b.emp_id
>>
>>
>> "Germano Silva" <germano-silva_at_home.com> wrote in message
>> news:99q3g1$h67$1_at_saturn.services.brown.edu...
>> > The following should work .
>> >
>> > update employee
>> > set fname = b.fname,
>> >      lname = b.lname
>> > from employee a, employee_bk b
>> > where a.emp_id = b.emp_id
>> >
>> > "CSC" <jcheong_at_cooper.com.hk> wrote in message
>> > news:99pqks$75f28_at_imsp212.netvigator.com...
>> > > In Oracle, I can issue the following command to update table:
>> > >
>> > > update employee a
>> > > set    (a.fname,a.lname) =
>> > > (select b.fname,b.lname
>> > > from employee_bk b
>> > > where a.emp_id = b.emp_id )
>> > >
>> > > What is the corresponding command in Informix and SQLServer?
>> > >
>> > >
>> > >
>> > >
>> > >
>> > > --
>> > > e-Consultant
>> > > http://www.asl.com.hk/employment.htm
>> > > http://www.eroom.com
>> >
>> >
>>
>>

-- 
e-Consultant
http://www.asl.com.hk/employment.htm
http://www.eroom.com