SELECT query in WHERE clause of UPDATE query

Question

Possible Duplicate:
Mysql error 1093 - Can’t specify target table for update in FROM clause

I am getting an error when putting SELECT query in WHERE clause of UPDATE query.

my Query is like this :

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 Error will be shown is like this :

"You can't specify target table 'm' for update in FROM clause"

I have attached a snap shot of the error display.

enter image description here

Please anyone can help me in this problem?

Thank You in Advance

Please post the code directly instead of a (hard to read) screenshot. That will also help answerers copy and correct what you have. — Paul Bellora, Nov 08 '12 at 06:52
Here is it .... http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause — Miqdad Ali, Nov 08 '12 at 06:54

score 7 · Accepted Answer · answered Nov 08 '12 at 06:57

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
)

or by using 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'

Yupyyyyyy....Thanks Dear It works perfectly as I need... Thanks Bro.... — Hardik, Nov 08 '12 at 07:03

score 2 · Answer 2 · edited May 23 '17 at 11:45

2

In MySQL, you can't modify the same table which you use in the SELECT part. This behaviour is documented at: http://dev.mysql.com/doc/refman/5.6/en/update.html

The reference

https://stackoverflow.com/a/45498/1225190

edited May 23 '17 at 11:45

Community

1
1

answered Nov 08 '12 at 06:54

Miqdad Ali

6,129
7
31
50

You mean to say that We can't update the same table using select query? there is not a way to achieve this using single query – Hardik Nov 08 '12 at 06:56
Please refer this answer dude... http://stackoverflow.com/a/45498/1225190 . this is a duplicate question – Miqdad Ali Nov 08 '12 at 06:57

SELECT query in WHERE clause of UPDATE query

2 Answers2

Linked

Related