UPDATE查询的WHERE子句中的SELECT查询
问题描述:
Possible Duplicate:
Mysql error 1093 - Can’t specify target table for update in FROM clause
将SELECT查询放在UPDATE查询的WHERE子句中时出现错误.
I am getting an error when putting SELECT query in WHERE clause of UPDATE query.
我的查询是这样的:
UPDATE `subschedulesseats` m
SET m.studentid='1'
WHERE m.`seatid`= (
SELECT h.`seatid`
FROM `subschedulesseats` h
WHERE h.`sessiontime`='02:30~04:00'
ORDER BY h.`seatid` ASC
LIMIT 2,1
)
AND错误将显示如下:
AND Error will be shown is like this :
您不能在FROM子句中指定目标表'm'进行更新"
"You can't specify target table 'm' for update in FROM clause"
我已附上错误显示的快照.
I have attached a snap shot of the error display.
请问有人可以帮助我解决这个问题吗?
Please anyone can help me in this problem?
先谢谢您
答
实际上,您可以通过将其包装在子查询中来进行更新(因此为结果创建临时表)
Actually you can update it by wrapping it in a subquery (thus creating temporary table for the result)
UPDATE `subschedulesseats` m
SET m.studentid='1'
WHERE m.`seatid`=
(
SELECT seatID
FROM
(
SELECT h.`seatid`
FROM `subschedulesseats` h
WHERE h.`sessiontime`='02:30~04:00'
ORDER BY h.`seatid` ASC
LIMIT 2,1
) s
)
或使用JOIN
UPDATE `subschedulesseats` m
INNER JOIN
(
SELECT seatID
FROM
(
SELECT h.`seatid`
FROM `subschedulesseats` h
WHERE h.`sessiontime`='02:30~04:00'
ORDER BY h.`seatid` ASC
LIMIT 2,1
) s
) t ON m.seatID = t.seatID
SET m.studentid = '1'