PostgreSQL delete with inner join

Sql Problem Overview

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?

Sql Solutions

Solution 1 - Sql

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';

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');

Solution 2 - Sql

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');

Solution 3 - Sql

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

Solution 4 - Sql

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';

Solution 5 - Sql

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,

use NOT EXISTS instead of IN
and WITH for large subqueries

Content Type	Original Author	Original Content on Stackoverflow
Question	dude	View Question on Stackoverflow
Solution 1 - Sql	Omesh	View Answer on Stackoverflow
Solution 2 - Sql	dude	View Answer on Stackoverflow
Solution 3 - Sql	Mahesh	View Answer on Stackoverflow
Solution 4 - Sql	Robert Casey	View Answer on Stackoverflow
Solution 5 - Sql	Ivan Rave	View Answer on Stackoverflow