0

I have two tables, Table_A (DB1) and Table_A (DB2) in different databases (DB1 and DB2 respectively). In these two tables, price and quantity may differ (or may be the same) even if the item_id and store is the same. 

                **Table_A (DB1)**   

**item_id**  **store**   **price**   **quantity** 
     1           A            1           1
     2           B            2           4
     1           C            1           1
     3           A            1           1
     3           C            2           1
     2           A            2           3
     3           B            2           2



                 **Table_A (DB2)**  

**item_id**  **store**   **price**   **quantity**            
     1           B            1           2
     2           A            2           3

Now I want to delete all such records from Table_A (DB1) for which the item_id is not present in Table_A (DB2) (remember, these tables may have different price and quantity for a given combination of item_id and store). The table I intend to get is as shown below (Table_A (DB1)-updated):

                **Table_A (DB1)-updated**   

**item_id**  **store**   **price**   **quantity** 
     3           A            1           1
     3           C            2           1
     3           B            2           2

To select such item_ids from Table_A (DB1) for which the item_id is absent in Table_A (DB2), I am using the following query: 

SELECT DISTINCT item_id FROM DB1.Table_A
WHERE item_id NOT IN (SELECT DISTINCT item_id FROM DB2.Table_A);

The above query gives me list of item_id absent in Table_A (DB2) (but present in Table_A (DB1)).

Now I wish to delete all the records from Table_A (DB1) with item_ids that we obtained in above step. I also want to do the same in just one go. I am trying to run the following query but it returns an error. 

DELETE FROM DB1.Table_A WHERE item_id IN 
(SELECT DISTINCT item_id FROM DB1.Table_A
WHERE item_id NOT IN (SELECT DISTINCT item_id FROM DB2.Table_A));

The error I get is:

Error Code: 1093. You can't specify target table 'Table_A' for update in FROM clause

Please help. Thank you.

DEarTh
  • 975
  • 1
  • 7
  • 18
Devendra Mishra
  • 19
  • 1
  • 1
  • 5
  • check it here... https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause – DEarTh Jul 19 '17 at 05:10
  • While Bill's comment does answer your question w.r.t to the problem you faced, your query is doing unnecessary distincts. Try using a simple query like this and have an index on item_id column DELETE FROM DB1.Table_A WHERE EXISTS (SELECT * FROM DB2.Table_B WHERE Table_B.item_ID = Table_A.Item_ID) – DeeJay14 Jul 19 '17 at 05:18

0 Answers0