CODEWITHSUNDEEP
mysql
user-pj
user-pj

Reputation: 28

SQL query UPDATE statement syntax error

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

Upvotes: 0

Views: 183

Answers (1)

juergen d
juergen d

Reputation: 204746

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
)

Upvotes: 2

Related Questions