Alias in UPDATE statment

Posted in 2011

Sams George — Thu, 20 Oct 2011 20:44:50 — source: IIUG Forums & Mailing Lists

Dear All, How do i run the following SQL without any errors in INFORMIX - UPDATE customer1 SET fname=E.fname, lname=E.lname FROM customer E WHERE E.customer_num='101' Thanks

↪ replying to Sams George

Jonathan Leffler — Thu, 20 Oct 2011 23:09:45 — source: IIUG Forums & Mailing Lists

On Thu, Oct 20, 2011 at 13:44, Sams George <sams.george1971@gmail.com>wrote: > How do I run the following SQL without any errors in INFORMIX - > > UPDATE customer1 > SET > fname=E.fname, > lname=E.lname > FROM customer E > WHERE E.customer_num='101' > UPDATE Customer1 SET (fname, lname) = ((SELECT e.fname, e.lname FROM Customer AS E WHERE E.Customer_Num = '101')) WHERE Customer_Num = '101'; The second WHERE clause is probably what you need -- otherwise, every customer is going to be given the same name as customer 101 in the main Customer table. IIRC, the double parentheses are crucial to this syntax. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --00151774036a30555c04afc30dac

↪ replying to Jonathan Leffler

Sams George — Fri, 21 Oct 2011 04:48:27 — source: IIUG Forums & Mailing Lists

Thanks Leffler. It worked. Regards, George On Fri, Oct 21, 2011 at 4:39 AM, Jonathan Leffler < jonathan.leffler@gmail.com> wrote: > On Thu, Oct 20, 2011 at 13:44, Sams George <sams.george1971@gmail.com > >wrote: > > > How do I run the following SQL without any errors in INFORMIX - > > > > UPDATE customer1 > > SET > > fname=E.fname, > > lname=E.lname > > FROM customer E > > WHERE E.customer_num='101' > > > > UPDATE Customer1 > SET (fname, lname) = ((SELECT e.fname, e.lname FROM Customer AS E WHERE > E.Customer_Num = '101')) > WHERE Customer_Num = '101'; > > The second WHERE clause is probably what you need -- otherwise, every > customer is going to be given the same name as customer 101 in the main > Customer table. > > IIRC, the double parentheses are crucial to this syntax. > > -- > Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> > Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org > "Blessed are we who can laugh at ourselves, for we shall never cease to be > amused." > > --00151774036a30555c04afc30dac > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec51f9965ca8c0604afc7c759