Re: Update table from another table

From: CSC <jcheong_at_cooper.com.hk>
Date: 29 Mar 2001 11:51:22 GMT
Message-ID: <99v7jq$rvu6@imsp212.netvigator.com>

Your statement does not work at all

In comp.databases.sybase Dave Leather <david.leather_at_energis-squared.com> wrote:
> could make it simpler and a bit more efficient
>
> update employee
> set (fname,lname) = (SELECT E1.fname,E1.lname FROM employee_bk as E1
> WHERE employee.emp_id = E1.emp_id)
> where exists (SELECT 'exists' FROM employee_bk E1
> WHERE employee.emp_id = E1.emp_id);
>
>
>
> "CSC" <jcheong_at_cooper.com.hk> wrote in message
> news:99u83t$mra9_at_imsp212.netvigator.com...

>> 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

>
>

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

Received on Thu Mar 29 2001 - 05:51:22 CST