Re: Update table from another table

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
Received on Thu Mar 29 2001 - 02:32:47 CST