有数据表Table,其中有a,b2个字段,并且a和b为联合主键,a为String类型,b为int型,
如下所示:
+------------------+
| a | b |
+------------------+
| M1 | 1 |
| M1 | 2 |
| M1 | 3 |
| M1 | 4 |
| M2 | 1 |
| M2 | 2 |
| M3 | 1 |
+-----------------+现在要更新b字段中的值,当 a = 'M1‘ 时,b的所有值都加1,
这是我原先写的sql语句,
update Table set b = (b+1) where a = 'M1'
由于a和b是联合主键
当执行更新的时候报错,主键冲突,
求达人们赐教!!!
如下所示:
+------------------+
| a | b |
+------------------+
| M1 | 1 |
| M1 | 2 |
| M1 | 3 |
| M1 | 4 |
| M2 | 1 |
| M2 | 2 |
| M3 | 1 |
+-----------------+现在要更新b字段中的值,当 a = 'M1‘ 时,b的所有值都加1,
这是我原先写的sql语句,
update Table set b = (b+1) where a = 'M1'
由于a和b是联合主键
当执行更新的时候报错,主键冲突,
求达人们赐教!!!
UPDATE (SELECT id FROM t1 ORDER BY id DESC) a INNER JOIN t1 b
ON a.id=b.id
SET b.id=a.id+1
Duplicate entry '20037-1' for key 'PRIMARY'
ON a.b=b.b
SET b.b=a.b+1;
ERROR 1062 (23000): Duplicate entry 'M1-5' for key 'PRIMARY'
mysql>
这是执行结果,还是有错啊,
+----------+------------+
| USER_KEY | LIST_ORDER |
+----------+------------+
| 20036 | 1 |
| 20037 | 0 |
| 20037 | 1 |
| 20038 | 1 |
| 20038 | 4 |
| 20039 | 2 |
| 20040 | 1 |
| 20041 | 0 |
| 20041 | 1 |
| 20042 | 0 |
| 20042 | 1 |
| 20043 | 0 |
| 20043 | 1 |
| 20044 | 0 |
| 20044 | 1 |
| 20045 | 0 |
| 20045 | 1 |
| 20046 | 1 |
| 20047 | 1 |
| 20048 | 1 |
+----------+------------+
20 rows in set (0.00 sec)mysql> update (select USER_KEY,LIST_ORDER from T_ALF_USER_AUTH_HISTORY order by USER_KEY,LIST_ORDER desc)a inner join T_ALF_USER_AUTH_HISTORY b on a.USER_KEY = b.USER_KEY set b.LIST_ORDER = a.LIST_ORDER +1;
ERROR 1062 (23000): Duplicate entry '20037-2' for key 'PRIMARY'
mysql>
CREATE TABLE `T_ALF_USER_AUTH_HISTORY`
(
`USER_KEY` NUMERIC NOT NULL,
`LIST_ORDER` NUMERIC NOT NULL,
`AUTHORISATION` BLOB,
CONSTRAINT `PK_ALF_USER_AUTH_HISTORY` PRIMARY KEY (`USER_KEY`, `LIST_ORDER`)
);
INNER JOIN T_ALF_USER_AUTH_HISTORY b
ON a.LIST_ORDER = b.LIST_ORDER AND a.USER_KEY=b.`USER_KEY` SET b.LIST_ORDER = a.LIST_ORDER +1;测试通过
我加上where以后,又会出现冲突的问题
mysql> update (select USER_KEY,LIST_ORDER from T_ALF_USER_AUTH_HISTORY order by USER_KEY,LIST_ORDER desc)a inner join T_ALF_USER_AUTH_HISTORY b on a.LIST_ORDER = b.LIST_ORDER set b.LIST_ORDER = a.LIST_ORDER +1 where b.USER_KEY=20041;
mysql> update (select USER_KEY,LIST_ORDER from T_ALF_USER_AUTH_HISTORY order by USER_KEY,LIST_ORDER desc)a inner join T_ALF_USER_AUTH_HISTORY b on a.LIST_ORDER = b.LIST_ORDER and a.USER_KEY=20041 set b.LIST_ORDER = a.LIST_ORDER +1;
ERROR 1062 (23000): Duplicate entry '20041-2' for key 'PRIMARY'
INNER JOIN T_ALF_USER_AUTH_HISTORY b
ON a.LIST_ORDER = b.LIST_ORDER AND a.USER_KEY=b.`USER_KEY` SET b.LIST_ORDER = a.LIST_ORDER +1
测试通过
你这`USER_KEY’和b.`USER_KEY`可不可以写成USER_KEY和b.USER_KEY呢,为什么啊
什么意思,2个字段才能确定1条记录
不加单引号的USER_KEY和b.USER_KEY也是2个字段啊
update Table set b = (b+1) where a = 'M1' order by b desc;
这句话也可以,但是不知道在其他数据库中可不可以用,
我这个语句同时要兼容4个数据库的
MSSQL,MYSQL,HSQL,ORCAL