RE: is this possible to do in one sql

Another has already posted a simple decode pair to accomplish this.

However:

you wrote

• I could do in two steps, but it prefer one step if possible, because the real table is big:

That being the case, if you have existing indexes on either of these columns a where predicate and separate updates might actually be a much smaller update,
and of course if you use the decode pair, at least add where notflag = '1' OR evidence = '2'

Without indexes you'll still do a tablescan, but at least you won't update every row, although you'll still update column values you don't need to when the OR is only half true. The less total data you can change, the better, but somewhere this is going to cross over with the cost of a double scan and updating some rows twice. I don't believe either solution is ideal for all cases of the pairwise value frequencies of your tables.

Three scans might actually do better where first you do the paired update, especially if you can substantially cache the table somewhere.

where notflag = '1' AND evidence = '2'

then the individual assignments
where notflag = '1'

where evidence = '2'

mwf

-----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 1: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:58:51 CST