RE: is this possible to do in one sql

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Fri, 17 Dec 2004 19:08:52 +0100
Message-Id: <20041217180903.4783944C0AC@ha-smtp2.tiscali.nl>

you could try this:
update t1

set    notflag  = case notflag  if '1' then null else notflag  end
,      evidence = case evidence if '2' then null else evidence end
where  notflag  = '1'
or     evidence = '2';

Cheers,
Lex.

Tom Kyte Seminar: http://www.naturaljoin.nl/events/seminars.html

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Guang Mei
Sent: Friday, December 17, 2004 19:01
To: Oracle-L-freelists
Subject: is this possible to do in one sql

Hi,

I have both oracle 8i and 9i running. I want to update two columns in a table with two different conditions. Is it possible to do it as one sql statment?

For example,

create table t1 (NOTFLAG CHAR(1), EVIDENCE CHAR(1));

insert into t1 (NOTFLAG,EVIDENCE ) values ('1','1'); insert into t1 (NOTFLAG,EVIDENCE ) values ('2','2'); commit;

select * from t1;

I want to do this (but it do not work): update t1 set NOTFLAG = null where NOTFLAG = '1', EVIDENCE =null where EVIDENCE ='2';
I could do in two steps, but it prefer one step if possible, because the real table is big: update t1 set NOTFLAG = null where NOTFLAG = '1', update t1 set EVIDENCE =null where EVIDENCE ='2';

Thanks,

Guang

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Fri Dec 17 2004 - 12:11:44 CST