0

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

Community
  • 1
  • 1
Joao Paulo
  • 3
  • 2
  • 1
    Isn't it a lot simpler to do `UPDATE cdrs SET tf=1 WHERE calltype=11`? – apokryfos Oct 20 '16 at 09:18
  • Hi @apokryfos, Yes, that's true! I was stick to the idea that have to use the primary key for updates but using the calltype, makes all sense logically. Thank you very much! – Joao Paulo Dec 05 '16 at 01:13

3 Answers3

2

You can do simple request : 

UPDATE cdrs SET tf = 1 WHERE calltype = 11;
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
Abdelilah EL BOUZEKRI EL IDRIS
  • 23
  • 4
  • Thank you very much. That really makes sense, as also pointed in other answers. I was thinking only about using primary key to make updates in the table. Thank you very much. – Joao Paulo Dec 05 '16 at 01:15
0

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
apokryfos
  • 38,771
  • 9
  • 70
  • 114
  • Thank you. I like this answer as it also adds information on temporary tables, than can be useful when the same kind of issue happens. – Joao Paulo Dec 05 '16 at 01:22
0

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

Ahmad
  • 5,551
  • 8
  • 41
  • 57
missingsemicolon
  • 480
  • 1
  • 10
  • 23
  • Thanks a lot for your answer. Now, with some answers pointing out to the same thing, I am thinking why I was considering only the primary key as the reference for updates. Best regards. – Joao Paulo Dec 05 '16 at 01:16