Re: Update table from another table

From: BP Margolin <bpmargo_at_attglobal.net>
Date: Thu, 29 Mar 2001 11:52:15 -0500
Message-ID: <3ac366ba_4@news3.prserv.net>

Dave,

While ANSI SQL-92 supports row constructors, SQL Server doesn't (at least not yet).

---

BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which can be cut and pasted into Query Analyzer is appreciated.

"Dave Leather" <david.leather_at_energis-squared.com> wrote in message news:99us79$kub$1_at_newsg4.svr.pol.co.uk...
> 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 - 10:52:15 CST