CODEWITHSUNDEEP
mysqlsqlmysql-error-1064
jela
jela

Reputation: 1469

Why does this DELETE query fail when the equivalent SELECT works?

I have two tables, products(product_id) and sex(product_id,sex_id). I am trying to DELETE rows from both tables that have a product_id such that there exists a row in the sex table with this product_id and sex=1. So far I have

CREATE TEMPORARY TABLE tempTable
      SELECT p.product_id  
        FROM products AS p
  INNER JOIN sex AS s
          ON p.product_id = s.product_id 
       WHERE s.sex = 1;

 DELETE FROM products AS p
NATURAL JOIN tempTable AS t

The DELETE query throws this error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NATURAL JOIN tempTable AS t' at line 2

This SELECT query works fine:

SELECT * FROM products
NATURAL JOIN tempTable

Why does the SELECT query work, but the DELETE query does not?

Upvotes: 2

Views: 957

Answers (1)

glglgl
glglgl

Reputation: 91149

Because DELETE has a different syntax. You have to specify from which table(s) you want to delete.

So do one of

DELETE p, t FROM products AS p NATURAL JOIN tempTable AS t;
DELETE p FROM products AS p NATURAL JOIN tempTable AS t;
DELETE t FROM products AS p NATURAL JOIN tempTable AS t;

Upvotes: 5

Related Questions