9 Replies Latest reply: Dec 21, 2009 3:51 AM by user8819407 RSS

    SQL command for UPDATE of multiple rows

    user8819407
      Hi,
      how to write SQL command to updating multiple rows in one statement? I tried as follows from TOAD which worked:

      UPDATE TableA SET a = 'x' WHERE b = 'a';
      UPDATE TableA SET a = 'x' WHERE b = 'b;
      UPDATE TableA SET a = 'x' WHERE b = 'c';
      UPDATE TableA SET a = 'x' WHERE b = 'd';
      ...

      But when I created same command and runned it from my asp.net (C#) application it didn't work, how come? It cause an ORA-00911: invalid character problem.

      My solution now is that I create one update command, insert into database, create next update command, insert into database, and so weither. This creates a lot of pressure on the server I guess and couldn't be the most effective way to write/run this update query.

      Any ideas/recommendations how to solve above is highly appreciated.

      Thx in advance.
        • 1. Re: SQL command for UPDATE of multiple rows
          Karthick_Arp
          user8819407 wrote:
          Hi,
          how to write SQL command to updating multiple rows in one statement? I tried as follows from TOAD which worked:

          UPDATE TableA SET a = 'x' WHERE b = 'a';
          UPDATE TableA SET a = 'x' WHERE b = 'b;
          UPDATE TableA SET a = 'x' WHERE b = 'c';
          UPDATE TableA SET a = 'x' WHERE b = 'd';
          ...
          Just write as 1 statement
          update tableA set a = 'x' where b in ('a','b','c','d')
          • 2. Re: SQL command for UPDATE of multiple rows
            user8819407
            Thx, my update is a little bit more complex, I have several Where conditions (example below), can I adapt your proposal in some way?

            UPDATE TableA SET a = 'v' WHERE b = 'a' AND c = 'aa' AND d = 'aaa';
            UPDATE TableA SET a = 'x WHERE b = 'b' AND c = 'bb' AND d = 'bbb';
            UPDATE TableA SET a = 'y' WHERE b = 'c' AND c = 'cc' AND d = 'ccc';
            UPDATE TableA SET a = 'z' WHERE b = 'd' AND c = 'dd' AND d = 'ddd';

            Note! new (SET) value is also different from row to row.

            Edited by: user8819407 on 2009-dec-21 00:40
            • 3. Re: SQL command for UPDATE of multiple rows
              Karthick_Arp
              user8819407 wrote:
              Thx, my update is a little bit more complex, I have several Where conditions (example below), can I adapt your proposal in some way?

              UPDATE TableA SET a = 'x' WHERE b = 'a' AND c = 'aa' AND d = 'aaa';
              UPDATE TableA SET a = 'x' WHERE b = 'b' AND c = 'bb' AND d = 'bbb';
              UPDATE TableA SET a = 'x' WHERE b = 'c' AND c = 'cc' AND d = 'ccc';
              UPDATE TableA SET a = 'x' WHERE b = 'd' AND c = 'dd' AND d = 'ddd';
              update tableA 
                 set a = 'x'
               where (b='a' and c='aa' and d='aaa')
                  or (b='b' and c='bb' and d='bbb')
                  or (b='c' and c='cc' and d='ccc')
                  or (b='d' and c='dd' and d='ddd')
              • 4. Re: SQL command for UPDATE of multiple rows
                user8819407
                Note! new (SET) value is also different from row to row.

                UPDATE TableA SET a = 'v' WHERE b = 'a' AND c = 'aa' AND d = 'aaa';
                UPDATE TableA SET a = 'x WHERE b = 'b' AND c = 'bb' AND d = 'bbb';
                UPDATE TableA SET a = 'y' WHERE b = 'c' AND c = 'cc' AND d = 'ccc';
                UPDATE TableA SET a = 'z' WHERE b = 'd' AND c = 'dd' AND d = 'ddd';
                • 5. Re: SQL command for UPDATE of multiple rows
                  Karthick_Arp
                  user8819407 wrote:
                  Note! new (SET) value is also different from row to row.

                  UPDATE TableA SET a = 'v' WHERE b = 'a' AND c = 'aa' AND d = 'aaa';
                  UPDATE TableA SET a = 'x WHERE b = 'b' AND c = 'bb' AND d = 'bbb';
                  UPDATE TableA SET a = 'y' WHERE b = 'c' AND c = 'cc' AND d = 'ccc';
                  UPDATE TableA SET a = 'z' WHERE b = 'd' AND c = 'dd' AND d = 'ddd';
                  in that case do it as a anonymous block
                  BEGIN
                  .. your update statements here..
                  END;
                  • 6. Re: SQL command for UPDATE of multiple rows
                    vansul
                    Update tableA
                    set a=decode(b,'a',decode(c,
                    'aaa',decode(d,'aaaa',v',a),a),
                    decode(b,'b',decode(c,
                    'aaa',decode(d,'aaaa',x',a),a),
                    decode(b,'c',decode(c,
                    'aaa',decode(d,'aaaa',y',a),a),a)

                    Edited by: vansul on 21 Dec, 2009 2:58 PM
                    • 7. Re: SQL command for UPDATE of multiple rows
                      vansul
                      Update tableA
                      set a=decode(b,'a',decode(c,
                      'aaa',decode(d,'aaaa',v',a),a),
                      decode(b,'b',decode(c,
                      'aaa',decode(d,'aaaa',x',a),a),
                      decode(b,'c',decode(c,
                      'aaa',decode(d,'aaaa',y',a),a),a)

                      Edited by: vansul on 21 Dec, 2009 3:01 PM
                      • 8. Re: SQL command for UPDATE of multiple rows
                        vansul
                        Update tableA
                        set a=decode(b,'a',decode(c,
                        'aaa',decode(d,'aaaa',v',a),a),
                        decode(b,'b',decode(c,
                        'aaa',decode(d,'aaaa',x',a),a),
                        decode(b,'c',decode(c,
                        'aaa',decode(d,'aaaa',y',a),a),a)
                        • 9. Re: SQL command for UPDATE of multiple rows
                          user8819407
                          Hi Karthick,
                          du you mean as follows will work?

                          Begin
                          UPDATE TableA SET a = 'v' WHERE b = 'a' AND c = 'aa' AND d = 'aaa';
                          UPDATE TableA SET a = 'x WHERE b = 'b' AND c = 'bb' AND d = 'bbb';
                          UPDATE TableA SET a = 'y' WHERE b = 'c' AND c = 'cc' AND d = 'ccc';
                          UPDATE TableA SET a = 'z' WHERE b = 'd' AND c = 'dd' AND d = 'ddd';
                          End;