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

Question

I am trying to run this query in mysql

UPDATE studentassessment sa SET sa.issubmitted=FALSE WHERE 
sa.studentassessmentid <> 
    (SELECT studentassessmentid FROM studentassessment sa1 WHERE 
    sa.StudentId=sa1.StudentId
    AND sa.AssessmentPeriodId=sa1.AssessmentPeriodId 
    ORDER BY sa1.studentassessmentid DESC LIMIT 0, 1) 
AND sa.studentid=568;

which gives me this error

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

I found number of this type of questions but they are specific to the queries for which they are asked so didn't help me much, from one of the answers of SO I tried to change the query like this

UPDATE studentassessment sa SET issubmitted=FALSE WHERE 
sa.studentassessmentid <> 
    (
      SELECT studentassessmentid FROM 
        (
          SELECT studentassessmentid FROM studentassessment sa1 WHERE 
              sa.StudentId=sa1.StudentId
          AND sa.AssessmentPeriodId=sa1.AssessmentPeriodId 
          ORDER BY sa1.studentassessmentid DESC LIMIT 0, 1
         )  AS T
    )
AND sa.studentid=568;

Then I started getting this error

Unknown column 'sa.StudentId' in 'where clause'

Any help?

Edit

This also didn't work

UPDATE studentassessment sa
INNER JOIN 
(SELECT studentassessmentid FROM studentassessment sa1 WHERE 
    sa.StudentId=sa1.StudentId
    AND sa.AssessmentPeriodId=sa1.AssessmentPeriodId 
    ORDER BY sa1.studentassessmentid DESC LIMIT 0, 1) T
ON sa.studentassessmentid<>T.StudentAssessmentId 
SET sa.issubmitted=0
WHERE sa.studentid=568;

Vyktor · Answer 1 · 2013-04-16T11:21:39.853

1

MySQL has that limitation; you can't update table which are you selecting from and you ain't doing nothing about it.

Currently, you cannot update a table and select from the same table in a subquery.

However you can do this:

CREATE TEMPORARY TABLE studentassessmentids (
    `id` INT,
    PRIMARY KEY (`id`)
);

LOCK TABLE studentassessment WRITE;
INSERT INTO studentassessmentids (SELECT ... prepare your ids);

UPDATE studentassessment sa
SET sa.issubmitted=FALSE
WHERE sa.studentassessmentid NOT IN (
    SELECT id
    FROM studentassessmentids AS ids
    WHERE ids.id = sa.studentassessmentid
)

UNLOCK TABLES;
DROP TEMPORARY TABLE studentassessmentids;

I wrote the code from the top of my had, so it won't work directly, but hopefully you'll be able to adopt the idea.

And if you could rewrite your query to group of JOIN statements, like (example from mysql.com):

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

It should work, but I don't see how you can implement NOT IN with joins without writing hardly readable code.

edited Apr 16 '13 at 11:21

answered Apr 16 '13 at 11:15

Vyktor

20,559
6
64
96

Thanks! Actually temporary table concept was there in my mind but I have to pass the query to someone to run so I was thinking if I could do it in a single query itself. So there is no way except temporary table? – Pawan Nogariya Apr 16 '13 at 11:17
@PawanNogariya you can do this also by using joins, but I don't see how you can do that in this particular case. Anyway added it to answer. – Vyktor Apr 16 '13 at 11:22
Sorry I don't see any join in your answer although I too tried with join that didn't work, I added it to my question – Pawan Nogariya Apr 16 '13 at 11:25
@PawanNogariya `UPDATE items,mothns ... WHERE items.id=month.id;` is implicit `INNER JOIN`, it's just an example from mysql site... – Vyktor Apr 16 '13 at 11:27

Pawan Nogariya · Answer 2 · 2013-04-16T11:41:14.010

Found the answer

   UPDATE studentassessment dest,
    (
       SELECT studentassessmentid,assessmentperiodid 
        FROM studentassessment sa WHERE sa.studentid=568
        AND sa.studentassessmentid<>
            (
               SELECT studentassessmentid FROM studentassessment s 
                WHERE s.studentid=568 
                AND sa.assessmentperiodid=s.assessmentperiodid
                ORDER BY studentassessmentid DESC LIMIT 1
            )

    )src

    SET dest.issubmitted=FALSE 
    WHERE dest.studentassessmentid=src.studentassessmentid

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

2 Answers2

Linked