why oracle can update pk=pk+1,but MySQL can not 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 does MySQL says

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

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

Answer 1

it seems oracle is either smarter about the execution order, or only checks constraints after applying the update to all rows. anyhow , the mysql example can be fixed by specifying the order on the update.

 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

Trying to update the first row to (2,3) is a duplicate key since (2,4) still has the old values. The workaround for this behavior is to reverse the Order and start with the largest index, changing (2,4) -> (3,4) and then (1,3) -> (2,3), and avoiding the collision altogether.

Answer 2

In Relational Databases, there is conceptually limited meaning (not to say not at all) to update your primary key.

Oracle implemented this, whereas MySQL didn't. You discovered it.

If you really want to do what you intend, solution is to insert new record, then remove previous.

To do it on the whole table, better create a temporary table. Here is how it looks:

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;