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

0
votes

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 ?

2
mysqlsubquerysql-delete

2 Answers

1
votes

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.

1
votes

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.