MySQL UPDATE语句中使用SELECT,报错

Su.E 发布于 2014/01/13 16:46
阅读 1K+
收藏 2

SQL语句如下:

UPDATE task T,
 (SELECT MIN(start) AS selected_value from task where parentId=:id) AS ST, 
 (SELECT MAX(end) AS selected_value from task where parentId=:id) AS EN 
   SET T.star = ST.selected_value,
         T.end = EN.selected_value
               WHERE T.id =:id

有时候会报出如下错误

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

怎么解?

加载中
1
八宝旗
八宝旗

对ST,EN中的task起个别名就行

UPDATE task T,
 (SELECT MIN(start) AS selected_value from task AS t1 where parentId=:id) AS ST, 
 (SELECT MAX(end) AS selected_value from task AS t1 where parentId=:id) AS EN 
   SET T.star = ST.selected_value,
         T.end = EN.selected_value
               WHERE T.id =:id

1
Wentasy
Wentasy

引用来自“Su.E”的答案

引用来自“八宝旗”的答案

对ST,EN中的task起个别名就行

UPDATE task T,
 (SELECT MIN(start) AS selected_value from task AS t1 where parentId=:id) AS ST, 
 (SELECT MAX(end) AS selected_value from task AS t1 where parentId=:id) AS EN 
   SET T.star = ST.selected_value,
         T.end = EN.selected_value
               WHERE T.id =:id

多谢指点,这是为什么呢?

MySQL语法要求这样。

使用MySQL进行DELETE FROM或者UPDATE操作时,若子查询的 FROM 字句和更新/删除对象使用同一张表,会出现错误。这里有一个变通的解决办法,可以通过多加一层SELECT别名表来变通解决。比如DELETE FROM test WHERE id=(SELECT max(id) FROM (SELECT * FROM test) AS t),但是这样的效率是极低的。这也算是MySQL的一个坑吧。

0
Su.E
Su.E

引用来自“八宝旗”的答案

对ST,EN中的task起个别名就行

UPDATE task T,
 (SELECT MIN(start) AS selected_value from task AS t1 where parentId=:id) AS ST, 
 (SELECT MAX(end) AS selected_value from task AS t1 where parentId=:id) AS EN 
   SET T.star = ST.selected_value,
         T.end = EN.selected_value
               WHERE T.id =:id

多谢指点,这是为什么呢?
返回顶部
顶部