PostgreSQL delete with inner join

240
votes
DELETE B.* 
FROM   m_productprice B  
       INNER JOIN m_product C ON B.m_product_id = C.m_product_id 
WHERE  C.upc = '7094' AND B.m_pricelist_version_id = '1000020'

i am getting the following error PostgreSQL 8.2.11

ERROR:  syntax error at or near "B"
LINE 1: DELETE B.* from m_productprice B  INNER JOIN m_product C ON ...

i tried giving 

DELETE B from m_productprice B  INNER JOIN m_product C ON B....
 ERROR:  syntax error at or near "B"

i tried giving

ERROR:  syntax error at or near "INNER"
LINE 1: DELETE from m_productprice B  INNER JOIN m_product C ON B.m_...

what is the problem with my query?

5
sqlpostgresql
8.2? You should upgrade as soon as possible. That version is no longer supported. And please read the manual: there is no INNER JOIN available for the DELETE statement: postgresql.org/docs/8.2/static/sql-delete.html - a_horse_with_no_name
any alternate method for executing this query without inner join - dude
See the manual, there is an example for exactly that. - a_horse_with_no_name
@a_horse_with_no_name In the current 8.2 documentation it does not explicitly state that you cannot use a JOIN with DELETE. It suggests using their alternative non-standard USING syntax and otherwise suggests a WHERE but it does not mention anywhere that JOIN is not supported. Given that JOINs in DELETE clauses are allowed in MySQL and SQL Server, Postgres is the odd one out. Maybe they have their reasons, but they should be explicit postgresql.org/docs/8.2/sql-delete.html - icc97
8.2 is not the "current" documentation. A syntax diagram only documents the available option. Therefor any option not included in the syntax diagram is automatically not supported. The SQL standard does not include any option to "join" tables in a DELETE statement. So Postgres' USING option is just as non-standard as the JOIN option MySQL and SQL Server use. Neither of them defines the "standard" and e.g. Oracle and DB2 have not option at all to "join" other tables - a_horse_with_no_name

5 Answers

350
votes
DELETE 
FROM m_productprice B  
     USING m_product C 
WHERE B.m_product_id = C.m_product_id AND
      C.upc = '7094' AND                 
      B.m_pricelist_version_id='1000020';

or

DELETE 
FROM m_productprice
WHERE m_pricelist_version_id='1000020' AND 
      m_product_id IN (SELECT m_product_id 
                       FROM m_product 
                       WHERE upc = '7094');
61
votes

This worked for me:

DELETE from m_productprice
WHERE  m_pricelist_version_id='1000020'
       AND m_product_id IN (SELECT m_product_id
                            FROM   m_product
                            WHERE  upc = '7094');
44
votes

Another form that works with Postgres 9.1+ is combining a Common Table Expression with the USING statement for the join.

WITH prod AS (select m_product_id, upc from m_product where upc='7094')
DELETE FROM m_productprice B
USING prod C
WHERE B.m_product_id = C.m_product_id 
AND B.m_pricelist_version_id = '1000020';
43
votes

If you have more than one join you could use comma separated USING statements:

DELETE 
FROM 
      AAA AS a 
USING 
      BBB AS b,
      CCC AS c
WHERE 
      a.id = b.id 
  AND a.id = c.id
  AND a.uid = 12345 
  AND c.gid = 's434sd4'

Reference

27
votes

Just use a subquery with INNER JOIN, LEFT JOIN or smth else:

DELETE FROM m_productprice
WHERE m_product_id IN
(
  SELECT B.m_product_id
  FROM   m_productprice  B
    INNER JOIN m_product C 
    ON   B.m_product_id = C.m_product_id
  WHERE  C.upc = '7094' 
  AND    B.m_pricelist_version_id = '1000020'
)

to optimize the query,