本文介绍了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查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-26 08:17
查看更多