CODEWITHSUNDEEP
mysqlsql
Joao Paulo
Joao Paulo

Reputation: 3

How to use a subquery in UPDATE query in MySQL within FROM clause

I have to update multiple rows in a table in a MySQL db, where the condition to find the rows to be updated are in the same table.

For instance, I have a table called "cdrs". There is a column in the table called "tf", that is the one I have to update. The column that has the condition to update or not the row is "calltype". "recordId" is the key for that table. The query I am using is this one:

UPDATE cdrs
SET tf = 1
WHERE recordId in (SELECT recordId from cdrs WHERE calltype = 11);

But with this query, I get the following error:

Error Code: 1093. You can't specify target table 'cdrs' for update in FROM clause.

I searched here and, to be honest, found many posts on this issue but all of them deal with this issue when the same table is in the SET clause, not the WHERE. For instance, I found this post: MySQL Error 1093 - Can't specify target table for update in FROM clause

I tried to use that idea but no success.

Does anyone had the same problem and found a solution? I appreciate any help in solving this issue.

Thank you very much, Joao Paulo

Upvotes: 0

Views: 71

Answers (3)

missingsemicolon
missingsemicolon

Reputation: 500

It is as simple as UPDATE cdrs SET tf = 1 WHERE calltype = 11;

Upvotes: 0

Abdelilah EL BOUZEKRI EL IDRIS
Abdelilah EL BOUZEKRI EL IDRIS

Reputation: 23

You can do simple request : 

UPDATE cdrs SET tf = 1 WHERE calltype = 11;

Upvotes: 2

apokryfos
apokryfos

Reputation: 40681

The generic answer to the question is, you can't use the same table in a subquery than you do in the main update query, you can get around this issue by doing a trick:

UPDATE cdrs
SET tf = 1
WHERE recordId in (SELECT recordId from (SELECT * FROM cdrs) innerCdrs WHERE calltype = 11);

This works because instead of using table cdrs you're creating a temporary table based on query SELECT * FROM cdrs.

However in your case your query can be greatly simplified to 

UPDATE cdrs SET tf=1 WHERE calltype=11

Upvotes: 0

Related Questions