Update statement without using FROM clause

Posted in 1999

Topics: Installation, Setup & Upgrades, Server Administration

jvnet@excite.com — Wed, 13 Oct 1999 22:24:02 GMT — source: Informix-list mailing list archive (1991-1998)

Being new to Informix I just discovered that I cannot use the FROM Clause unless AD and XP options are installed on Dynamic Server 7.3. How could I proceed to update a table otherwise ? Example: update dba.pay set priority = 'P' from dba.bemp, dba.pay where dba.bemp.coid = dba.pay.coid and dba.bemp.locid = dba.pay.locid and dba.bemp.empid = dba.pay.empid and dba.bemp.homedept = dba.pay.deptid and dba.bemp.job = dba.pay.jobid; Thank you JV Sent via Deja.com http://www.deja.com/ Before you buy.

↪ replying to jvnet@excite.com

Christophe MAUMONT — Thu, 14 Oct 1999 08:25:26 +0200 — source: Informix-list mailing list archive (1991-1998)

why not : update dba.pay set priority = 'P' where dba.bemp.coid in (select dba.pay.coid from dba.pay where dba.bemp.locid = dba.pay.locid and dba.bemp.empid = dba.pay.empid and dba.bemp.homedept = dba.pay.deptid and dba.bemp.job = dba.pay.jobid); jvnet@excite.com a 'crit dans le message <7u30pi$emk$1@nnrp1.deja.com>... >Being new to Informix I just discovered that I cannot use the FROM >Clause unless AD and XP options are installed on Dynamic Server 7.3. >How could I proceed to update a table otherwise ? > >Example: > >update dba.pay >set priority = 'P' >from dba.bemp, dba.pay >where dba.bemp.coid = dba.pay.coid >and dba.bemp.locid = dba.pay.locid >and dba.bemp.empid = dba.pay.empid >and dba.bemp.homedept = dba.pay.deptid >and dba.bemp.job = dba.pay.jobid; > >Thank you >JV > > >Sent via Deja.com http://www.deja.com/ >Before you buy.

↪ replying to Christophe MAUMONT

jvnet@excite.com — Thu, 14 Oct 1999 14:49:15 GMT — source: Informix-list mailing list archive (1991-1998)

This results in the following error: "SQL error 522 Table (dba.bemp_coid) not selected in query". Any further help would be much appreciated. In article <7u3t7n$5gf$1@jaydee.iway.fr>, "Christophe MAUMONT" <informatique@univitis.fr> wrote: > why not : > > update dba.pay > set priority = 'P' > where dba.bemp.coid in (select dba.pay.coid from dba.pay where > dba.bemp.locid = dba.pay.locid > and dba.bemp.empid = dba.pay.empid > and dba.bemp.homedept = dba.pay.deptid > and dba.bemp.job = dba.pay.jobid); > > jvnet@excite.com a 'crit dans le message <7u30pi$emk$1@nnrp1.deja.com>... > >Being new to Informix I just discovered that I cannot use the FROM > >Clause unless AD and XP options are installed on Dynamic Server 7.3. > >How could I proceed to update a table otherwise ? > > > >Example: > > > >update dba.pay > >set priority = 'P' > >from dba.bemp, dba.pay > >where dba.bemp.coid = dba.pay.coid > >and dba.bemp.locid = dba.pay.locid > >and dba.bemp.empid = dba.pay.empid > >and dba.bemp.homedept = dba.pay.deptid > >and dba.bemp.job = dba.pay.jobid; > > > >Thank you > >JV > > > > > >Sent via Deja.com http://www.deja.com/ > >Before you buy. > > Sent via Deja.com http://www.deja.com/ Before you buy.

↪ replying to Christophe MAUMONT

Art S. Kagel — Thu, 14 Oct 1999 12:17:41 -0400 — source: Informix-list mailing list archive (1991-1998)

You got your tables confused but that's otherwise correct, also best to make the sub-query non-corrollated if possible. Fixed below: Christophe MAUMONT wrote: > > why not : Why not indeed! > update dba.pay set priority = 'P' where dba.pay.coid in ( select dba.bemp.coid from dba.bemp, dba.pay where dba.bemp.locid = dba.pay.locid and dba.bemp.empid = dba.pay.empid and dba.bemp.homedept = dba.pay.deptid and dba.bemp.job = dba.pay.jobid ); Art S. Kagel > > jvnet@excite.com a écrit dans le message <7u30pi$emk$1@nnrp1.deja.com>... > >Being new to Informix I just discovered that I cannot use the FROM > >Clause unless AD and XP options are installed on Dynamic Server 7.3. > >How could I proceed to update a table otherwise ? > > > >Example: > > > >update dba.pay > >set priority = 'P' > >from dba.bemp, dba.pay > >where dba.bemp.coid = dba.pay.coid > >and dba.bemp.locid = dba.pay.locid > >and dba.bemp.empid = dba.pay.empid > >and dba.bemp.homedept = dba.pay.deptid > >and dba.bemp.job = dba.pay.jobid; > > > >Thank you > >JV > > > > > >Sent via Deja.com http://www.deja.com/ > >Before you buy.

↪ replying to Art S. Kagel

Christophe MAUMONT — Fri, 15 Oct 1999 08:49:16 +0200 — source: Informix-list mailing list archive (1991-1998)

Oups !! CTRL-C / CTRL-V too fast. Sorry, i didn't test it before sending. Art S. Kagel a 'crit dans le message <38060225.C707FA0C@bloomberg.net>... >You got your tables confused but that's otherwise correct, also best to >make the sub-query non-corrollated if possible. Fixed below: > >Christophe MAUMONT wrote: >> >> why not : >Why not indeed! >> >update dba.pay >set priority = 'P' >where dba.pay.coid in ( > select dba.bemp.coid > from dba.bemp, dba.pay > where dba.bemp.locid = dba.pay.locid > and dba.bemp.empid = dba.pay.empid > and dba.bemp.homedept = dba.pay.deptid > and dba.bemp.job = dba.pay.jobid >);> >Art S. Kagel > >> >> jvnet@excite.com a 'crit dans le message <7u30pi$emk$1@nnrp1.deja.com>... >> >Being new to Informix I just discovered that I cannot use the FROM >> >Clause unless AD and XP options are installed on Dynamic Server 7.3. >> >How could I proceed to update a table otherwise ? >> > >> >Example: >> > >> >update dba.pay >> >set priority = 'P' >> >from dba.bemp, dba.pay >> >where dba.bemp.coid = dba.pay.coid >> >and dba.bemp.locid = dba.pay.locid >> >and dba.bemp.empid = dba.pay.empid >> >and dba.bemp.homedept = dba.pay.deptid >> >and dba.bemp.job = dba.pay.jobid; >> > >> >Thank you >> >JV >> > >> > >> >Sent via Deja.com http://www.deja.com/ >> >Before you buy.