Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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?
                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
                Aug 1, 2012 at 6:53
                @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
                Mar 10, 2021 at 7:00
                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
                Mar 10, 2021 at 7:13
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'); 
                The a.id = b.id part is basically the join.  I overlooked this, but it's the key piece, thank you!
– Adam Hughes
                Oct 6, 2022 at 13:47
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'); 
                Yes this is what postgres recommend as the 'standard' way to do it in the delete documentation notes: "A more standard way to do it is: DELETE FROM films WHERE producer_id IN (SELECT id FROM producers WHERE name = 'foo');". So the good thing of this is that this sql will work in other databases.
– icc97
                Oct 7, 2022 at 8:09
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';
                CTEs maybe 9.1+ but USING was allowed in 8.2 as well postgresql.org/docs/8.2/sql-delete.html
– icc97
                Mar 10, 2021 at 7:06
    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
Essentially everything mentioned here is mentioned in the docs, but no-one is specifying exactly what. So this is what the current (v15) DELETE docs says:
Notes
PostgreSQL lets you reference columns of other tables in the WHERE condition by specifying the other tables in the USING clause. For example, to delete all films produced by a given producer, one can do:
DELETE FROM films USING producers
 WHERE producer_id = producers.id AND producers.name = 'foo';
What is essentially happening here is a join between films and producers, with all successfully joined films rows being marked for deletion. This syntax is not standard. A more standard way to do it is:
DELETE FROM films
 WHERE producer_id IN (SELECT id FROM producers WHERE name = 'foo');
In some cases the join style is easier to write or faster to execute than the sub-select style.
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.