Clever Geek Handbook

Is it possible to delete multiple rows in MySQL if the target table is in a partition?

I am using PHPMYAdmin and MySQL database. I would like to remove from table 1masterinventory all rows (PK RID) that are not in 1amazoninventory (PK RID). I feel like this query should work, but I am getting error "# 1093 - You cannot specify the target table '1masterinventory' to update in the FROM clause.

 DELETE FROM 1masterinventory 
 WHERE rid IN
     (
        SELECT rid 
        FROM 1masterinventory 
        WHERE rid NOT IN
         (
           SELECT rid FROM 
           1amazoninventory WHERE 1
         )
    )
+3


source to share


1 answer


SQL Fiddle :



DELETE m
FROM 1masterinventory m
LEFT JOIN 1amazoninventory a ON a.rid = m.rid
WHERE a.rid IS NULL;
+2


source






More articles:


All Articles