J. Mike Rollins (Sparky) [rollins@wfu.edu]
  CISSP, GIAC GPEN
Hyperbola New
My Cats New
Kitty New
Mike is on
  LinkedIn
  FaceBook
BackYardGreen.Net
HappyPiDay.com
Green Cycle Design Group
CamoTruck.Net
  Resume  
  My Stuff  
  Art  
  My Truck  
  People  
Electronics
Jacob's Ladder
Scripts
Math
Notes
   MySQL FK
   smb
   MIME
   PHP/Perl Reference
   SQL update
   Base64
My House
My Cars
My Cats New
My Jokes
Pi Poetry
pumpkin
Toro Mower
Development
Speed of a Piston
Not a Pipe
Linux
















SQL update

My first program that interfaced with a database used Perl and Oracle. Later I used Perl with MySQL and encountered a learning curve. The following demonstrates one of the differences that caught me by surprise.

With Oracle, the following update statements produce consistent results.

    drop table mytest;
    create table mytest (pk number, a number, b number);
    
    insert into mytest (pk, a, b) values (1,1,1);
    
    update mytest
       set a = a + 1,
           b = a + 1
     where pk = 1;
    
    select * from mytest;
    
            PK          A          B
    ---------- ---------- ----------
             1          2          2
    
    drop table mytest;
    create table mytest (pk number, a number, b number);
    
    insert into mytest (pk, a, b) values (1,1,1);
    
    update mytest
       set b = a + 1,
           a = a + 1
     where pk = 1;
    
    select * from mytest;
    
            PK          A          B
    ---------- ---------- ----------
             1          2          2
    
    
The results are not consistent using MySQL. It appears that with MySQL, the modification made by a=a+1 takes effect immediately. But with Oracle, the new value of a is not available until after the statement is completely processed.
    drop table mytest;
    create table mytest (pk integer, a integer, b integer);
    
    insert into mytest (pk, a, b) values (1,1,1);
    
    update mytest
       set a = a + 1,
           b = a + 1
     where pk = 1;
    
    select * from mytest;
    
    +------+------+------+
    | pk   | a    | b    |
    +------+------+------+
    |    1 |    2 |    3 |
    +------+------+------+
    
    drop table mytest;
    create table mytest (pk integer, a integer, b integer);
    
    insert into mytest (pk, a, b) values (1,1,1);
    
    update mytest
       set b = a + 1,
           a = a + 1
     where pk = 1;
    
    select * from mytest;
    
    +------+------+------+
    | pk   | a    | b    |
    +------+------+------+
    |    1 |    2 |    2 |
    +------+------+------+