CODEWITHSUNDEEP
mysqlsql
POV
POV

Reputation: 12015

MySQL table was specified twice?

Why I got an error when try to execute this query:

UPDATE ordersperformers AS t1 SET t1.Status = 1 
where not exists (
SELECT 1 FROM ordersperformers 
   WHERE ordersperformers.Orders_Id = 3 
    AND (ordersperformers.Status = 1 OR ordersperformers.Status = 2))
    AND t1.Orders_Id = 3 AND t1.Users_Id = 5;

Why MySQL does not realize that I work with table ordersperformers?

Error is:

#1093 - Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data.

If execute query:

UPDATE ordersperformers SET Status = 1 
where  ordersperformers.Status NOT IN (1,2)
AND Orders_Id = 3;

It will update anyway one row:

enter image description here

But I dont need update any rows cause table with Orders_Id = 3 has one row with Status = 1

Upvotes: 1

Views: 1721

Answers (1)

Zeki Gumus
Zeki Gumus

Reputation: 1484

You need to use EXISTS() if you want to update Status=1 with same order_id has at least one Status=1. Also because we do check with the same table you need put it to sub-query (yes, inside the bracket) to avoid conflict.

Simply you need to do something like this :

SCRIPT EDITED:

UPDATE ordersperformers as O1 
SET O1.Status = 1 
where  EXISTS 
            (SELECT 1
             FROM (select * from ordersperformers) AS O2
             WHERE O1.Orders_Id = O2.Orders_Id
             AND O2.Status = 1
             )

Upvotes: 2

Related Questions