This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Nov 21, 2012 8:51 AM by user515689 RSS

Update multiple statements in a sql script

user515689 Newbie
Currently Being Moderated
Hello Oracle forum users,

If I were to embed multiple update statements into an sql script, would it not look like this?

UPDATE tbl1 SET empid = 'AY61534' WHERE   empid = '0054928'
/
UPDATE tbl1  SET  empid  =  'SC84109' WHERE empid = '0062968'
 /
UPDATE  tbl1  SET empid  =  'MS05132' WHERE empid = '0064097'
/
 UPDATE  tbl1 SET  empid   =  'MJ84231' WHERE   empid = '0221564'
 /
UPDATE  tbl1  SET  empid  =  'BD71098' WHERE   empid = '0274614'
/
UPDATE  tbl1  SET  empid  =    'SM84078' WHERE   empid = '0582411'
/
 UPDATE  tbl1 SET  empid  =   'BS094821' WHERE  empid = '0582585'
 / 
  UPDATE  tbl1  SET empid  = 'BK53421' WHERE   empid = '0583401'
  /
commit;
I'm getting errors at the semicolon.

I tried with the / only and no semicolon as well as with the semicolon.

Unfortunately, we're talking about several rows of hardcoded values where it's a one for one change. I ran a different update statement on it earlier, but even with columns indexed, it was taking 5-10 minutes or longer to update!

Also, would the above require any type of lock to be put on it beforehand?

I'm using Oracle 11g.

Any feedback is appreciated.

Thanks!
  • 1. Re: Update multiple statements in a sql script
    SomeoneElse Guru
    Currently Being Moderated
    I'm getting errors at the semicolon.
    Since we can't look over your shoulder, it's necessary for you to post any error numbers/messages you encounter.

    Your code works for me exactly as you posted it. (by working, I mean there are no syntax errors)
    SQL> create table tbl1 (empid varchar2(10));
    
    Table created.
    
    SQL> UPDATE tbl1 SET empid = 'AY61534' WHERE   empid = '0054928'
      2  /
    
    0 rows updated.
    
    SQL> UPDATE tbl1  SET  empid  =  'SC84109' WHERE empid = '0062968'
      2   /
    
    0 rows updated.
    
    SQL> UPDATE  tbl1  SET empid  =  'MS05132' WHERE empid = '0064097'
      2  /
    
    0 rows updated.
    
    SQL>  UPDATE  tbl1 SET  empid   =  'MJ84231' WHERE   empid = '0221564'
      2   /
    
    0 rows updated.
    
    SQL> UPDATE  tbl1  SET  empid  =  'BD71098' WHERE   empid = '0274614'
      2  /
    
    0 rows updated.
    
    SQL> UPDATE  tbl1  SET  empid  =    'SM84078' WHERE   empid = '0582411'
      2  /
    
    0 rows updated.
    
    SQL>  UPDATE  tbl1 SET  empid  =   'BS094821' WHERE  empid = '0582585'
      2   /
    
    0 rows updated.
    
    SQL>   UPDATE  tbl1  SET empid  = 'BK53421' WHERE   empid = '0583401'
      2    /
    
    0 rows updated.
    
    SQL> commit;
    
    Commit complete.
  • 2. Re: Update multiple statements in a sql script
    user515689 Newbie
    Currently Being Moderated
    OK, I'll post the error. Stand by.

    Edited by: user515689 on Nov 19, 2012 4:53 PM

    In the Toad SQL query window I get.
    ORA-00936: missing expression
    It flags it at the second Update line.

    In SQL Plus I get the same thing essentially:
    UPDATE table1 SET empid  =  'SC84092' WHERE empid = '0062968'
    *
    ERROR at line 3:
    ORA-00936: missing expression
    Which is the second UPDATE statement also. I've tried ending the statements with semicolons and with slashes. Either one seems to give me this error.

    Edited by: user515689 on Nov 19, 2012 5:00 PM
  • 3. Re: Update multiple statements in a sql script
    SomeoneElse Guru
    Currently Being Moderated
    In SQL Plus I get the same thing essentially:
    Your code worked fine for me in SQL Plus.

    I still can't see your session (we can only help with what you post), so I don't know what to tell you.
  • 4. Re: Update multiple statements in a sql script
    user515689 Newbie
    Currently Being Moderated
    OK, fair enough.

    Am I wrong in the assertion I have that I "should" be able to execute those updates in succession, one after another?
  • 5. Re: Update multiple statements in a sql script
    jeneesh Guru
    Currently Being Moderated
    user515689 wrote:
    OK, fair enough.

    Am I wrong in the assertion I have that I "should" be able to execute those updates in succession, one after another?
    you can..

    Did you read the first reply to your post?
  • 6. Re: Update multiple statements in a sql script
    user515689 Newbie
    Currently Being Moderated
    jeneesh wrote:
    user515689 wrote:
    OK, fair enough.

    Am I wrong in the assertion I have that I "should" be able to execute those updates in succession, one after another?
    you can..

    Did you read the first reply to your post?
    Yes, but it wasn't totally clear to me, sorry.

    I would just need commit at the end of it all?
  • 7. Re: Update multiple statements in a sql script
    user515689 Newbie
    Currently Being Moderated
    SomeoneElse wrote:
    In SQL Plus I get the same thing essentially:
    Your code worked fine for me in SQL Plus.

    I still can't see your session (we can only help with what you post), so I don't know what to tell you.
    Ok, this is kind of odd. Maybe you're able to explain it to me why it would work this way.

    If I copy and paste the contents, of the above, into SQL Plus, and hit enter, it runs as expected/it should.

    If I have the contents in a simpe .sql file, and either hit open or run, it gives those errors.

    Any reason that would cause it to work this way? I do not have a BEGIN/END block in the sql file, but I didn't think I needed one.

    Thanks.
  • 8. Re: Update multiple statements in a sql script
    sb92075 Guru
    Currently Being Moderated
    a shovel is a great tool for making a hole in the ground; but only when the "correct end" of the shovel is used to move the dirt.

    you are using the "wrong end" of toad

    Problem Exists Between Keyboard And Chair
  • 9. Re: Update multiple statements in a sql script
    user515689 Newbie
    Currently Being Moderated
    sb92075 wrote:
    a shovel is a great tool for making a hole in the ground; but only when the "correct end" of the shovel is used to move the dirt.

    you are using the "wrong end" of toad

    Problem Exists Between Keyboard And Chair
    The wrong end? Ok, if you say so.
  • 10. Re: Update multiple statements in a sql script
    SomeoneElse Guru
    Currently Being Moderated
    If I run your code (again, exactly as you posted it) as a .sql script it still works fine:
    SQL> @sqlfile
    
    0 rows updated.
    
    
    0 rows updated.
    
    
    0 rows updated.
    
    
    0 rows updated.
    
    
    0 rows updated.
    
    
    0 rows updated.
    
    
    0 rows updated.
    
    
    0 rows updated.
    
    
    Commit complete.
    You aren't posting your sql plus sessions so I can't tell what you're trying to do.
  • 11. Re: Update multiple statements in a sql script
    user515689 Newbie
    Currently Being Moderated
    SomeoneElse wrote:
    If I run your code (again, exactly as you posted it) as a .sql script it still works fine:
    SQL> @sqlfile
    
    0 rows updated.
    
    
    0 rows updated.
    
    
    0 rows updated.
    
    
    0 rows updated.
    
    
    0 rows updated.
    
    
    0 rows updated.
    
    
    0 rows updated.
    
    
    0 rows updated.
    
    
    Commit complete.
    You aren't posting your sql plus sessions so I can't tell what you're trying to do.
    That up above wasn't from my sessions? I sure thought it was. A few posts up?

    I'll provide again. Forgive me. Thought for sure I was looking at it. (several posts up)
    UPDATE  table1 SET  empID = 'TL80439' WHERE empID = '1605432'  
     /
    *UPDATE*  table1 SET  empID = 'TK83423' WHERE empID = '1614609'    
      /
    UPDATE  table1 SET  empID = 'AH73188' WHERE empID = '1632330'    
      /
    UPDATE  table1 SET  empID = 'NB50838' WHERE empID = '1725870'    
      /
    UPDATE  table1 SET  empID = 'JS16349' WHERE empID = '1729107'    
      /
    UPDATE  table1 SET  empID = 'GP34923' WHERE empID = '1730327'    
      /
    UPDATE  table1 SET  empID = 'AM01863' WHERE empID = '1733901'    
      /
    UPDATE  table1 SET  empID = 'DW23894' WHERE empID = '1742229'    
      /
    UPDATE  table1 SET  empID = 'SS80141' WHERE empID = '1758128'    
      /
    UPDATE  table1 SET  empID = 'BA71847' WHERE empID = '1805539'    
      /
    flags it at the bold and gives
    ORA-00936: missing expression
    The above is in a TOAD SQL Query window. It's not possible or encouraged to submit screen shots is it? At any rate.

    The below from SQL Plus :
    SQL> 
      1  UPDATE  table1 SET  empID = 'TL80439' WHERE empID = '1605432'  
      2   /
      3  UPDATE  table1 SET  empID = 'TK83423' WHERE empID = '1614609'    
      4    /
      5  UPDATE  table1 SET  empID = 'AH73188' WHERE empID = '1632330'    
      6    /
      7  UPDATE  table1 SET  empID = 'NB50838' WHERE empID = '1725870' 
      8    /
      9  UPDATE  table1 SET  empID = 'JS16349' WHERE empID = '1729107'  
     10    /
     11  UPDATE  table1 SET  empID = 'GP34923' WHERE empID = '1730327'    
     12    /
     13  UPDATE  table1 SET  empID = 'AM01863' WHERE empID = '1733901'   
     14    /
     15  UPDATE  table1 SET  empID = 'DW23894' WHERE empID = '1742229'
     16    /
     17  UPDATE  table1 SET  empID = 'SS80141' WHERE empID = '1758128'    
     18    /
     19  UPDATE  table1 SET  empID = 'BA71847' WHERE empID = '1805539'  
     20*   /
    UPDATE  table1 SET  empID  = 'TK83223' WHERE empID  = '1614609'
    *
    ERROR at line 3:
    ORA-00936: missing expression
    The above ..this is if I open the file, "condensed.sql" and then hit Run. I swear I've been able to do this before but maybe that was back in Ora10 and now I'm using 11g.

    -------------------
    NOW, if I just copy and paste the lines of code into SQL Plus, and hit enter, they run fine.
    SQL> UPDATE  table1 SET  empID = 'TL80439' WHERE empID = '1605432'  
        2   /
      
      1 row updated.
      
      SQL> UPDATE  table1 SET  empID = 'TK83423' WHERE empID = '1614609'  
        2    /
      
      1 row updated.
      
      SQL> UPDATE  table1 SET  empID = 'AH73188' WHERE empID = '1632330'  
        2    /
      
      1 row updated.
      
      SQL> UPDATE  table1 SET  empID = 'NB50838' WHERE empID = '1725870'  
        2    /
      
      1 row updated.
      
      SQL> UPDATE  table1 SET  empID = 'JS16349' WHERE empID = '1729107'  
        2    /
      
      1 row updated.
      
      SQL> UPDATE  table1 SET  empID = 'GP34923' WHERE empID = '1730327'  
        2    /
      
      1 row updated.
      
      SQL> UPDATE  table1 SET  empID = 'AM01863' WHERE empID = '1733901'  
        2    /
      
      1 row updated.
      
      SQL> UPDATE  table1 SET  empID = 'DW23894' WHERE empID = '1742229'  
        2    /
      
      1 row updated.
      
      SQL> UPDATE  table1 SET  empID = 'SS80141' WHERE empID = '1758128'  
        2    /
      
    1 row updated.
    This is what I'm having difficulty figuring out - why they'll run that way, and not from the sql script being called.

    I hope this explains it more clearly. I actually have several hundred to do which is why I prefer to just keep in a script, as well as for audit purposes.

    I figure someone out there knows better than I why that would be happening, although on the surface, to me, seems like basic SQL and that it would work either way. But it's not.

    Edited by: user515689 on Nov 20, 2012 1:51 PM
  • 12. Re: Update multiple statements in a sql script
    SomeoneElse Guru
    Currently Being Moderated
    The above ..this is if I open the file, "condensed.sql" and then hit Run.
    You lost me here. "Open" and "Run"?

    Are you using that ancient Windows version of SQL Plus? If you are, get rid of it. Use the command line version sqlplus.exe.

    Use the @ operator to run a file.

    SQL> @condensed.sql
  • 13. Re: Update multiple statements in a sql script
    user515689 Newbie
    Currently Being Moderated
    SomeoneElse wrote:
    The above ..this is if I open the file, "condensed.sql" and then hit Run.
    You lost me here. "Open" and "Run"?
    Open the file, filename.sql and click Run.
    >
    Are you using that ancient Windows version of SQL Plus? If you are, get rid of it. Use the command line version sqlplus.exe.
    YES, I believe I am. It's for the 10 client.
    >
    Use the @ operator to run a file.
    Ok, will do.
    >
    SQL> @condensed.sql
    Thanks.
  • 14. Re: Update multiple statements in a sql script
    sb92075 Guru
    Currently Being Moderated
    user515689 wrote:

    SQL> 
    1  UPDATE  table1 SET  empID = 'TL80439' WHERE empID = '1605432'  
    2   /
    3  UPDATE  table1 SET  empID = 'TK83423' WHERE empID = '1614609'    
    4    /
    5  UPDATE  table1 SET  empID = 'AH73188' WHERE empID = '1632330'    
    6    /
    7  UPDATE  table1 SET  empID = 'NB50838' WHERE empID = '1725870' 
    8    /
    9  UPDATE  table1 SET  empID = 'JS16349' WHERE empID = '1729107'  
    10    /
    11  UPDATE  table1 SET  empID = 'GP34923' WHERE empID = '1730327'    
    12    /
    13  UPDATE  table1 SET  empID = 'AM01863' WHERE empID = '1733901'   
    14    /
    15  UPDATE  table1 SET  empID = 'DW23894' WHERE empID = '1742229'
    16    /
    17  UPDATE  table1 SET  empID = 'SS80141' WHERE empID = '1758128'    
    18    /
    19  UPDATE  table1 SET  empID = 'BA71847' WHERE empID = '1805539'  
    20*   /
    UPDATE  table1 SET  empID  = 'TK83223' WHERE empID  = '1614609'
    *
    ERROR at line 3:
    ORA-00936: missing expression
    Please explain how/why posted error is for "empID = 'TK83223'" when that exact value does NOT exist in the posted SQL code?
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points