MySQL table was specified twice?

Question

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:

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

I faced with this problem it a lot of my queries, I dont have problems if run this query in SQL Database — POV, Dec 09 '18 at 18:37
Possible duplicate of [Table is specified twice, both as a target for 'UPDATE' and as a separate source for data in mysql](https://stackoverflow.com/questions/44970574/table-is-specified-twice-both-as-a-target-for-update-and-as-a-separate-source) — Himanshu, Dec 09 '18 at 18:39
use `select * from table` inside that query after update rather `as table` in order to avoid this mysql confusion as per the reference link above — Himanshu, Dec 09 '18 at 18:41
please refer the link in the above comment check the corrected answer hope that helps — Himanshu, Dec 09 '18 at 19:12
@OPV Have you thought that maybe you did not make clear which rows need to be updated and which not? — forpas, Dec 09 '18 at 19:17

Zeki Gumus · Accepted Answer · 2018-12-09T19:26:40.650

2

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
             )

edited Dec 09 '18 at 19:26

answered Dec 09 '18 at 18:39

Zeki Gumus

1,484
7
14

I have some rows with `Orders_Id` and `sers_Id`. I need update only if in table there is Status = 1 OR 2 for only `Orders_Id` – POV Dec 09 '18 at 18:44
That is incorrect I need use only `where Orders_Id = 3 ` Not `AND Users_Id = 5;`. – POV Dec 09 '18 at 18:46
@OPV Ok. Remove User_Id=5 from the filter. – Zeki Gumus Dec 09 '18 at 18:48
It again find row with `Status 0`, but I need update if there are not rows in table for `Orders_Id` where `Status = 1` altogether – POV Dec 09 '18 at 18:52
@OPV Could you select the table first please. What you see when you execute "select Status from ordersperformers where order_id=3"? – Zeki Gumus Dec 09 '18 at 18:55
I added result to question and explanation – POV Dec 09 '18 at 18:58
@OPV Ok. Order_Id is not unique and you want to update the other rows as status=1 if at least one of the same Order_Id has Status=1. Am I right? – Zeki Gumus Dec 09 '18 at 19:04
Sure, therefor I tried check this using where exists – POV Dec 09 '18 at 19:06
@OPV Ok. I have edited the query with using EXISTS() – Zeki Gumus Dec 09 '18 at 19:08
`Unknown column 'O1.Order_Id' in 'where clause'` – POV Dec 09 '18 at 19:09
I need `where not EXISTS ` – POV Dec 09 '18 at 19:11
@OPV Change the Order_Id to Orders_ID. – Zeki Gumus Dec 09 '18 at 19:11
`#1093 - Table 'O1' is specified twice, both as a target for 'UPDATE' and as a separate source for data` – POV Dec 09 '18 at 19:12
1

@OPV Probably we use same table in exists. I put it to sub-query. – Zeki Gumus Dec 09 '18 at 19:16

MySQL table was specified twice?

1 Answers1