CODEWITHSUNDEEP
mysqlsql
Sashi yadav
Sashi yadav

Reputation: 358

not able to update to update table as it is used in from clause in subquery

UPDATE qn_answers
SET `GRADE` = -100
WHERE `QSN_ID` IN (SELECT q.`ID` FROM `questions` q
LEFT JOIN qn_answers qa ON q.`ID` = qa.`QSN_ID`
WHERE q.`QSN_TYPE_ID` = 3 AND qa.`ISCORRECT` = 0 AND qa.`GRADE` = 0);

I'm trying to update a column based on the result from the join table , but it gives ERROR : 1093

Upvotes: 0

Views: 44

Answers (3)

sabhari karthik
sabhari karthik

Reputation: 1371

I think standard update also will work here. Try this:

UPDATE qa
SET `GRADE` = -100
FROM 
`questions` q
LEFT JOIN
`qn_answers` qa
ON
q.`ID` = qa.`QSN_ID`
WHERE q.`QSN_TYPE_ID` = 3 AND qa.`ISCORRECT` = 0 AND qa.`GRADE` = 0;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521804

I think you want an update join here, without a subquery:

UPDATE qn_answers qa
INNER JOIN questions q
    ON qa.QSN_ID = q.ID
SET GRADE = -100
WHERE
    q.QSN_TYPE_ID = 3 AND
    qa.ISCORRECT = 0  AND
    qa.GRADE = 0;

The error message has to do with that you are using the target table qn_answers as part of the join. You can't do this directly in a MySQL update.

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

use join update

UPDATE qn_answers a
join 
(SELECT q.`ID` FROM `questions` q
LEFT JOIN qn_answers qa ON q.`ID` = qa.`QSN_ID`
WHERE q.`QSN_TYPE_ID` = 3 AND qa.`ISCORRECT` = 0 AND qa.`GRADE` = 0
) b on a.`QSN_ID`=b.`ID`
SET `GRADE` = -100

Upvotes: 0

Related Questions