PostgreSQL delete with inner join

Question

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 — user330315
– user330315, Commented Aug 1, 2012 at 6:53
any alternate method for executing this query without inner join — dude
– dude, Commented Aug 1, 2012 at 6:55
@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
– icc97, Commented 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 — user330315
– user330315, Commented Mar 10, 2021 at 7:13

Omesh · Accepted Answer · 2012-08-01 07:01:51Z

503

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

edited Aug 1, 2012 at 7:01

answered Aug 1, 2012 at 6:46

Omesh

29.4k6 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

dude Over a year ago

@0mesh its for mysql .. my doubt is for sql and postgre sql

mgoldwasser Over a year ago

for larger tables, the first solution in this answer is potentially much faster.

Kostanos Over a year ago

The best answer, especially the first one, as it allows you to match by multiple fields.

icc97 Over a year ago

the second one will work across all databases

Douglas Gaskell Over a year ago

What if you don't have a key column to use due to some temporary factor? If I know the rows I want to delete, from the result of a query, but don't necessarily have some specific identifier for them, how?

|

Mahesh · Accepted Answer · 2018-12-14 00:51:19Z

97

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

answered Dec 14, 2018 at 0:51

Mahesh

4,0651 gold badge42 silver badges50 bronze badges

1 Comment

Adam Hughes Over a year ago

The a.id = b.id part is basically the join. I overlooked this, but it's the key piece, thank you!

fthiella · Accepted Answer · 2013-06-12 07:37:36Z

69

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

edited Jun 12, 2013 at 7:37

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

answered Aug 1, 2012 at 7:01

dude

5,0628 gold badges36 silver badges51 bronze badges

1 Comment

icc97 Over a year ago

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.

Community · Accepted Answer · 2016-02-26 18:45:54Z

57

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

edited Feb 26, 2016 at 18:45

CommunityBot

11 silver badge

answered Jul 30, 2015 at 18:41

Robert Casey

1,59119 silver badges20 bronze badges

1 Comment

icc97 Over a year ago

CTEs maybe 9.1+ but USING was allowed in 8.2 as well postgresql.org/docs/8.2/sql-delete.html

Alexander Farber · Accepted Answer · 2017-03-13 16:11:38Z

33

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

edited Mar 13, 2017 at 16:11

Alexander Farber

23.4k79 gold badges263 silver badges449 bronze badges

answered Feb 11, 2016 at 16:04

Ivan Rave

1,78718 silver badges15 bronze badges

Comments

icc97 · Accepted Answer · 2023-02-09 10:40:27Z

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.

samzna · Accepted Answer · 2023-06-02 05:22:26Z

1

By use of using , we can do that.

****

DELETE B.* 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'
------------------------------------------------------------------------

****

answered Jun 2, 2023 at 5:22

samzna

4354 silver badges8 bronze badges

Collectives™ on Stack Overflow

PostgreSQL delete with inner join

7 Answers 7

7 Comments

1 Comment

1 Comment

1 Comment

Comments

Notes

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

7 Comments

1 Comment

1 Comment

1 Comment

Comments

Notes

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related