Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195

    Unanswered: Update Query Help Needed

    I have a table that relies on Operation Numbers (RT_OP_NUM) and when an operation has been deleted, I need all operations with operations numbers greater than the one deleted to have their RT_OP_NUM value to have 10 taken away from it. Here is an example:

    Code:
    ROWS
    --------
    
    RT_IMKEY                    RT_OP_NUM
    ---------------------------------------------
    SHEAR                                10
    DEBURR                              20
    FORM                                  30
    HARDWARE                          40
    CLEAN                                 50
    PACK                                  60
    So, If I were to delete form, I need to change it to this:

    Code:
    ROWS
    ---------
    
    RT_IMKEY                     RT_OP_NUM
    ------------------------------------------------
    SHEAR                               10
    DEBURR                             20
    HARDWARE                        30
    CLEAN                               40
    PACK                                50
    Can someone help me out? Thanks, Jeremy
    Nothing better than a good ride.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Update Query Help Needed

    Strange requirement, but it's a strange world...

    Hopefully, RT_OP_NUM is not referenced in other tables, otherwise you need a cascading update?

    How about:

    Code:
    create or replace trigger trg
    after delete on mytab
    declare
      n integer := 0;
    begin
      for r in (select * from mytab order by rt_op_num)
      loop
        n := n+10;
        if r.rt_op_num != n then
          update mytab set rt_op_num = n where rt_imkey = r.rt_imkey;
        end if;
      end loop;
    end;
    /
    Note this is NOT a "for each row" trigger.

  3. #3
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    This is what works:

    Code:
    UPDATE RT SET RT_OP_NUM = RT_OP_NUM - 10 WHERE RT_IMKEY = something AND RT_QNUM = something AND RT_OP_NUM > something
    The somethings in the code are all variables placed into the mix during runtime of my program. It works fine in SQL*Plus but when I do the EXACT same thing in Visual Basic, it doesn't commit. I don't understand. I wouldn't mind creating a trigger. The RT_OP_NUM isn't referenced from within the database, only in my program. The trigger you gave me earlier...would I be able to modify it to accommodate for the code above? I've never done that in VB before. Thanks, Jeremy
    Nothing better than a good ride.

Posting Permissions

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