Why the oracle can update pk = pk + 1, but MySQL cannot update set pk = pk + 1

Question

ORACLE:

 create table t7(c1 number primary key,c2 number);
 insert into t7 values (1,3);
 insert into t7 values (2,4);
 commit;
 update t7 set c1=c1+1;
 commit ;
 select * from t7;

MySQL:

 create table t7(c1 int primary key,c2 int);
 insert into t7 values (1,3);
 insert into t7 values (2,4);
 select * from t7;
 update t7 set c1=c1+1;
 ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

Why MySQL says

update set pk=pk+1 :Duplicate entry '2' for key 'PRIMARY',

While ORACLE can do this update set pk=pk+1?

Answer 1

it seems that the oracle is either smarter about the execution order, or just checks the constraints after applying the update to all lines. anyway, the mysql example can be fixed by specifying the update order.

 create table t7(c1 int primary key,c2 int);
 insert into t7 values (1,3);
 insert into t7 values (2,4);
 select * from t7;
 update t7 set c1=c1+1 order by c1 desc;

see http://sqlfiddle.com/#!9/8611f4/1

(2,3) , (2,4) . , (2,4) → (3,4), (1,3) → (2,3) .

Answer 2

( ) .

Oracle , MySQL . .

, , , , .

, . :

create table t7_tmp(c1 int primary key,c2 int);
insert into t7_tmp (select c1+1,c2 from t7);
delete t7;
insert into t7 (select c1,c2 from t7_tmp);
drop table t7_tmp;