RE: is this possible to do in one sql
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <gmei@xxxxxxxxxx>, "Oracle-L-freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 17 Dec 2004 13:30:07 -0500
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
