SQL UPDATE with sub-query that references the same table in MySQL

Question

I'm trying to update a column's value in a bunch of rows in a table using UPDATE. The problem is that I need to use a sub-query to derive the value for this column, and it depends on the same table. Here's the query:

UPDATE user_account student
SET student.student_education_facility_id = (
   SELECT teacher.education_facility_id
   FROM user_account teacher
   WHERE teacher.user_account_id = student.teacher_id AND teacher.user_type = 'ROLE_TEACHER'
)
WHERE student.user_type = 'ROLE_STUDENT';

Ordinarily if teacher and student were in 2 different tables, mysql wouldn't complain. But since they are both using the same table, mysql spews out this error instead:

ERROR 1093 (HY000): You can't specify target table 'student' for update in FROM clause

Is there any way I can force mysql to do the update? I am 100% positive the from clause will not be affected as the rows are updated.

If not, is there another way I can write this update sql to achieve the same affect?

Thanks!

EDIT: I think I got it to work:

UPDATE user_account student
LEFT JOIN user_account teacher ON teacher.user_account_id = student.teacher_id
SET student.student_education_facility_id = teacher.education_facility_id
WHERE student.user_type = 'ROLE_STUDENT';

John Hartsock · Accepted Answer · 2010-11-24T15:31:38.003

44

Some reference for you http://dev.mysql.com/doc/refman/5.0/en/update.html

UPDATE user_account student 
INNER JOIN user_account teacher ON
   teacher.user_account_id = student.teacher_id 
   AND teacher.user_type = 'ROLE_TEACHER'
SET student.student_education_facility_id = teacher.education_facility_id

edited Nov 24 '10 at 15:31

answered Nov 24 '10 at 15:24

John Hartsock

85,422
23
131
146

6

It would be nice if there were two solutions: the specific and one a bit more abstract, with clearer table and column names, to simplify reading for people looking for a general answer, like me. In any case, both question and answer satisfy me, but I was suggesting it for a better understanding of the community. Thanks, and +1 to both – Nico Jul 05 '13 at 19:13
2

Totally agree with @Nico on this. That's why I provided an abstract example with clearer table and column names (http://stackoverflow.com/a/23772515/886539). I also upvoted John Hartsock's answer (helped me a lot create my example). – Simon Arnold May 21 '14 at 01:28

score 20 · Answer 2 · edited Feb 13 '17 at 18:26

20

Abstract example with clearer table and column names:

UPDATE tableName t1
INNER JOIN tableName t2 ON t2.ref_column = t1.ref_column
SET t1.column_to_update = t2.column_desired_value

As suggested by @Nico

Hope this help someone.

edited Feb 13 '17 at 18:26

Simke Nys

72
1
9

answered May 21 '14 at 01:21

Simon Arnold

15,849
7
67
85

1

For an abstract example, the ```ON t2.ref_column = t1.column_to_update``` clause should not refer to ```column_to_update```. The join criterion is likely based on a different column. – frankleonrose Feb 03 '17 at 04:23

score 5 · Answer 3 · edited Nov 10 '15 at 22:05

5

UPDATE user_account 
SET (student_education_facility_id) = ( 
    SELECT teacher.education_facility_id
    FROM user_account teacher
    WHERE teacher.user_account_id = teacher_id
    AND teacher.user_type = 'ROLE_TEACHER'
)
WHERE user_type = 'ROLE_STUDENT'

Above are the sample update query...

You can write sub query with update SQL statement, you don't need to give alias name for that table. give alias name to sub query table. I tried and it's working fine for me....

edited Nov 10 '15 at 22:05

JJD

50,076
60
203
339

answered Nov 20 '13 at 14:28

Ricky Patel

329
3
13

When no alias specified in sub query, doesn't it take the column from inner table? – Nish Jan 25 '14 at 11:54

score 2 · Answer 4 · edited Jun 07 '13 at 16:13

2

UPDATE user_account student

SET (student.student_education_facility_id) = (

   SELECT teacher.education_facility_id

   FROM user_account teacher

   WHERE teacher.user_account_id = student.teacher_id AND teacher.user_type = 'ROLE_TEACHER'

)

WHERE student.user_type = 'ROLE_STUDENT';

edited Jun 07 '13 at 16:13

MikO

18,243
12
77
109

answered Jun 07 '13 at 15:54

Sin2

413
3
5

score 2 · Answer 5 · answered Feb 23 '15 at 17:26

I needed this for SQL Server. Here it is:

UPDATE user_account 
SET student_education_facility_id = cnt.education_facility_id
from  (
   SELECT user_account_id,education_facility_id
   FROM user_account 
   WHERE user_type = 'ROLE_TEACHER'
) as cnt
WHERE user_account.user_type = 'ROLE_STUDENT' and cnt.user_account_id = user_account.teacher_id

I think it works with other RDBMSes (please confirm). I like the syntax because it's extensible.

The format I needed was this actually:

UPDATE table1 
SET f1 = cnt.computed_column
from  (
   SELECT id,computed_column --can be any complex subquery
   FROM table1
) as cnt
WHERE cnt.id = table1.id

Fantastic I used something similar and worked like a charm, thanks! (Microsoft SQL SERVER) — Pedro Emilio Borrego Rached, Sep 20 '16 at 15:31

score 0 · Answer 6 · edited May 27 '21 at 13:38

0

I needed to do a sum in the sub query and none of the solution was working. So I created a view on the main table and could access the same table from it.

Create user_account_VW as select * from user_account

UPDATE user_account
SET user_account.student_education_facility_id =
    (SELECT user_account_VW.education_facility_id
     FROM user_account_VW
     WHERE user_account_VW.user_account_id = user_account.teacher_id
         AND user_account_VW.user_type = 'ROLE_TEACHER')

edited May 27 '21 at 13:38

gurkan

884
4
16
25

answered May 27 '21 at 08:03

Olivier Quatre Bornes

1

Hi Olivier, Could you please add Tags to your question. Welcome to Stackoverflow :) – Ashraf Alshahawy May 27 '21 at 08:35

score -3 · Answer 7 · edited Jun 07 '12 at 17:04

-3

UPDATE user_account student, (
   SELECT teacher.education_facility_id as teacherid
   FROM user_account teacher
   WHERE teacher.user_account_id = student.teacher_id AND teacher.user_type = 'ROLE_TEACHER'
) teach SET student.student_education_facility_id= teach.teacherid WHERE student.user_type = 'ROLE_STUDENT';

edited Jun 07 '12 at 17:04

Beau Grantham

3,435
5
33
43

answered Jun 07 '12 at 11:56

Magizh

5

A little explanation is always better and make your answer more valuable. – ForceMagic Oct 21 '12 at 01:59

SQL UPDATE with sub-query that references the same table in MySQL

7 Answers7

Linked