update statement...

[muralees]

I am having a table say 'X' with 30 columns in it.
I am using MINUS operator to select few records in the same table. like
select a, b, c from X
where date_field = 'some_date'
minus
select a, b, c from X
where date_field = 'some_date - 1'.

I am using 20 of the 30 columns in the select statement.
I have declared a cursor on this select. In the cursor FOR loop
I want to update a flag field to 'Y' for all the records selected in the cursor. There is no other processing involved in it other than updating. This way of updating the records is taking more time as it is in Loop.
Is there any other way of doing this in single update.

I have tried to update like

update x set c = 'Y'
where (a,b,c) IN
(select a, b, c from X
where date_field = 'some_date'
minus
select a, b, c from X
where date_field = 'some_date - 1'
)

to avoid the cursor, but it is not working.

Any suggestions pls...
Thanks....Muralees

[Shestakov]

Question for u:

1) witch construction correct ?
date_field = 'some_date - 1' or
date_field = 'some_date' - 1
in ur real statment
2) what error code u got.

[muralees]

Hi Shestakov,

There is no problem with the select statement

select a, b, c from X
where date_field = 'some_date'
minus
select a, b, c from X
where date_field = 'some_date - 1'

it is working properly. In my question I have just put an example as some_date.. But I pass the actual dates in the qry, so it doesn't matter. The MINUS is working properly and say for example the result is some 6000 rows. I want to update all the 6000 rows with a flag field to 'Y' in one update statement.

Thanks..Muralees

[Shestakov]

Ok muralees!

about date - it was my first question.

second question was - what Oracle error code you have got?
because sintax seems correctly.
but if we going to execute a long update(delete,..) statments we should have reletive rollback segments.

[muralees]

I am not getting any Oracle error but the update statement says Zero rows updated, even if the select is resulting 6000 rows selected.
I am sure that I am giving all the fields in the IN clause of the update and in proper order as in the select statement.
In fact I have used this kind of update with 5 fields in the IN clause where it has worked properly.

[Shestakov]

This is should be a little error in source code. I dn't know what kind of error, but a little.

Try to use pl/sql block for this UPDATE, if u want it.

declare
cursor cr is
select a from X
where date_field = 'some_date'
minus
select a from X
where date_field = 'some_date - 1'
for update c;
begin
for r in cr loop
update x set c='y' where current of cr;
end loop;
commit;
end;