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 |
+------+------+------+
|
|
