MySQL Error You can't specify target table for update in FROM clause

Question

I have a 2 tables MST_customer and TRN_sales in my database with corrupt entries. The next query returns the corrupt entries:

SELECT TRN_sales.cust_no 
FROM MST_customer 
RIGHT OUTER JOIN TRN_sales 
ON MST_customer.cust_no = TRN_sales.cust_no 
WHERE MST_customer.cust_name IS NULL;

I tried to delete them executing:

DELETE FROM mydbB.TRN_sales
WHERE TRN_sales.cust_no IN (
  SELECT TRN_sales.cust_no
  FROM MST_customer
  RIGHT OUTER JOIN TRN_sales
  ON MST_customer.cust_no = TRN_sales.cust_no
  WHERE MST_customer.cust_name IS NULL
);

But I get the next error:

You can't specify target table 'TRN_sales' for update in FROM clause

How can I resolve this problem ?

Carsten Massmann · Accepted Answer · 2015-06-29 06:08:08Z

1

To be a bit more on "the safe side" you should specifiy the table (here: alias name s) you want to delete from like:

DELETE s FROM TRN_sales s
LEFT JOIN MST_customers ON MST.cust_no=TRN.cust_no
WHERE MST.cust_name IS NULL;

Personnaly I believe, this LEFT JOIN is easier to read, although of course you can do the same with your RIGHT JOIN version.

answered Jun 29, 2015 at 6:08

Carsten Massmann

28.4k3 gold badges25 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Zafar Malik Over a year ago

@cars10: May you elaborate how "delete s" is more safe than "delete TRN.*".....yes left join is more readbale but I just worked on Rin's query so that he feel easy as it was same like as his query...but there is no issue in both if you use left join or right join.

Carsten Massmann Over a year ago

Hi Zafar, your answer is (and always was) 100% correct! When I wrote my answer I misread your TRN.* as referring to the whole db. That was not the case, as you quite rightly only refer to the sales table in question. So from my point of view your answer is still the correct one and deserves to be the "chosen one" as it appeared first here. The only reason I kept my answer was to show an alternative way with LEFT JOIN instead of RIGHT JOIN. I just 'upped' your post anyway... ;-)

Zafar Malik Over a year ago

@cars10: No not at all, I was just curious if there is any major impact of alias.* and just alias in deletion under join. :)

Zafar Malik · Accepted Answer · 2015-06-29 05:40:37Z

1

Why you don't try below-

DELETE TRN.*
FROM MST_customer MST
RIGHT OUTER JOIN TRN_sales TRN
ON MST.cust_no = TRN.cust_no 
WHERE MST.cust_name IS NULL;

Note: For safe side keep backup of both tables before executing this query.

answered Jun 29, 2015 at 5:40

Zafar Malik

6,8723 gold badges23 silver badges31 bronze badges

1 Comment

Carsten Massmann Over a year ago

You do not need the *: DELETE TRN FROM ... will also work.

Collectives™ on Stack Overflow

MySQL Error You can't specify target table for update in FROM clause

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related