SQL query UPDATE statement syntax error

Question

I need to run a SQL UPDATE query like below

UPDATE table_1
SET field_1 = value_1
WHERE field_2 IN (
        SELECT t1.field_2
        FROM table_1 AS t1
            ,table_2 AS t2
        WHERE t2.field_3 = value_2
            AND t1.field_2 = t2.field_2
        );

But the issue is its is howing error like-

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

When I am running sub-query alone and putting its reslut in main query, then I am getting the results. Can anyone help?

Thanks in advance

juergen d · Accepted Answer · 2015-10-12 09:26:53Z

2

Like the error says, MySQL can't update and select the same table at the same time. But with another subquery in the middle you can do it anyway

UPDATE table_1 
SET field_1 = value_1 
where field_2 in 
(
  select * from
  (
    SELECT t1.field_2 
    from table_1 as t1
    join table_2 as t2 on t2.field_3 = value_2 
    and t1.field_2= t2.field_2
  ) tmp
)

edited Oct 12, 2015 at 9:26

answered Oct 12, 2015 at 9:26

juergen d

205k40 gold badges305 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Radu Gheorghiu Over a year ago

Can you please explain what is the point of the additional sub-query level?

user-pj Over a year ago

@juergen d, thanks a lot, it worked like a charm, but please explain what is the use of additonal sub-query level?

juergen d Over a year ago

The use is that you create a temp table with it and don't need to select from the table you are updating directly.

user-pj Over a year ago

Thanks @juergend, thats great

Collectives™ on Stack Overflow

SQL query UPDATE statement syntax error

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related