update statement...
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: update statement...

  1. #1
    Join Date
    Feb 2002
    Posts
    5

    Question

    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

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.

  3. #3
    Join Date
    Feb 2002
    Posts
    5
    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

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.


  5. #5
    Join Date
    Feb 2002
    Posts
    5
    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.

  6. #6
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width