select in update
update table1
set col1 =
(
select table2.col1
from table2
where table1.col2 = table2.col2
)
where ...
sometimes it can happen, that the select doesn't return a value. in this
case table1.col1 must be set to a defined constant value, say x.
i don't know, how to express this in informix-sql.
i tried:
set col1 = nvl ((select...), x)
or
set col1 = (select nvl (table2.col1, x) ....
but nothing seems right.
what can i do?
tia
uwe
use this:
update table1
set col1 =
(select table2.col1
from table2
where table1.col2 = table2.col2)
where ...
;
update table1
set col1 = "x"
where col2 not in (select col2 from table2)
;
In article <3A06EC95.DC4BFCCC@Dresdner-Bank.com>,
Uwe Doetzkies <Uwe.Doetzkies@Dresdner-Bank.com> wrote:
> update table1
> set col1 =
> (
> select table2.col1
> from table2
> where table1.col2 = table2.col2
> )
> where ...>
> sometimes it can happen, that the select doesn't return a value. in
this
> case table1.col1 must be set to a defined constant value, say x.
>
> i don't know, how to express this in informix-sql.
>
> i tried:
> set col1 = nvl ((select...), x)
> or
> set col1 = (select nvl (table2.col1, x) ....
>
> but nothing seems right.
> what can i do?
>
> tia
> uwe
>
Sent via Deja.com http://www.deja.com/
Before you buy.
In Standard SQL-92, the scalra subquery must either return a one column, one row reuslt set or an mepty reuslt set. The empty result set will be convewrted into a NULL. That means the two UPDATE soilution someone else posted will not work. The trick in SQL-92 is to tremember that subqueries are in parens and to use the COALESCE() funciton, which like the proprietary NVL() function, only much better since it takes a list of values. UPDATE Table1 SET col1 = COALESCE((SELECT col1 FROM Table2 WHERE Table1.col2 = Table2.col2), x) WHERE ...; --CELKO-- Joe Celko, SQL Guru & DBA at Trilogy When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which can be cut and pasted into Query Analyzer is appreciated. Sent via Deja.com http://www.deja.com/ Before you buy.
Hi Joe, I looked up IDS 7.31.UC7 and IDS.2000 9.21.UC1 but did not find any routine called COALESCE. Does it mean that this SQL-92 routine is not implemented by Informix yet? How about forthcoming 9.30? Joe Celko wrote: > In Standard SQL-92, the scalra subquery must either return a one > column, one row reuslt set or an mepty reuslt set. The empty result > set will be convewrted into a NULL. That means the two UPDATE > soilution someone else posted will not work. > > The trick in SQL-92 is to tremember that subqueries are in parens and > to use the COALESCE() funciton, which like the proprietary NVL() > function, only much better since it takes a list of values. > > UPDATE Table1 > SET col1 > = COALESCE((SELECT col1 > FROM Table2 > WHERE Table1.col2 = Table2.col2), x) > WHERE ...; > > --CELKO-- > Joe Celko, SQL Guru & DBA at Trilogy > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) > which can be cut and pasted into Query Analyzer is appreciated. > > Sent via Deja.com http://www.deja.com/ > Before you buy. -- Mit freundlichen Grüßen / with best regards :-) +===================================Mail-Client: Netscape V4.75=+ | Ernst Sauerwein Siemens Busines Services | | =============== IT Services ISC-D 144 | | Microsoft Certified OLTP-Systems | | Professional INFORMIX® Database Support | | Tel: +49 89 3601 2699 Area South Europe | | FAX: +49 89 3601 2402 Berliner Str.95,III/D046 | | EMAIL: mailto:ernst.sauerwein@mch.siemens.de | | FTP: ftp://ftp.siemens.de/ (external) | | ftp://MB184060.mch.sni.de/ (internal) | | WWW: http://its.mch.sni.de/ (external) | | http://MB184060.mch.sni.de/ (internal) (v private) | | http://www.stud.uni-muenchen.de/~yaling.mo/Welcome.htm | +===============================================================+
Ernst Sauerwein wrote:
> I looked up IDS 7.31.UC7 and IDS.2000 9.21.UC1 but did not find any routine
> called COALESCE. Does it mean that this SQL-92 routine is not implemented
> by Informix yet? How about forthcoming 9.30?
There is no COALESCE that I know of (though I've not double checked the
manuals this week looking for it). I believe there is an NVL in 7.31
and 9.21; if there isn't, it is not dreadfully difficult to write a
stored procedure that does the job adequately.
> Joe Celko wrote:
> > In Standard SQL-92, the scalra subquery must either return a one
> > column, one row reuslt set or an mepty reuslt set. The empty result
> > set will be convewrted into a NULL. That means the two UPDATE
> > soilution someone else posted will not work.
> >
> > The trick in SQL-92 is to tremember that subqueries are in parens and
> > to use the COALESCE() funciton, which like the proprietary NVL()
> > function, only much better since it takes a list of values.
> >
> > UPDATE Table1
> > SET col1
> > = COALESCE((SELECT col1
> > FROM Table2
> > WHERE Table1.col2 = Table2.col2), x)
> > WHERE ...;
-- @(#)$Id: nvl_int.spl,v 1.1 1996/08/26 18:33:11 johnl Exp $
--
-- nvl_integer: return v1 if it is not null else return v2
CREATE PROCEDURE nvl_integer(v1 INTEGER, v2 INTEGER DEFAULT 0)
RETURNING INTEGER;
DEFINE rv INTEGER;
IF v1 IS NOT NULL THEN
LET rv = v1;
ELSE
LET rv = v2;
END IF
RETURN rv;
END PROCEDURE;
This is the integer-only version. You can write one taking and
returning VARCHAR values, and Informix will take care of the conversions
on input and output. Obviously, as Joe points out, it does not take a
list of values.
--
Yours,
Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h>
Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN
"I don't suffer from insanity; I enjoy every minute of it!"