RE: is this possible to do in one sql

This will work :

update t1
set NOTFLAG=decode(NOTFLAG,'1',null,NOTFLAG}, EVIDENCE=decode(EVIDENCE,'2',null,EVIDENCE) where NOTFLAG = '1' or EVIDENCE='2';

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

From: Guang Mei [mailto:gmei_at_incyte.com] Sent: Friday, December 17, 2004 12:01 PM 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:15:40 CST