本文介绍了UPDATE查询的WHERE子句中的SELECT查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
将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'
这篇关于UPDATE查询的WHERE子句中的SELECT查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!