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: http://www.postgresql.org/docs/8.2/static/sql-delete.html — , Aug 01 '12 at 06:53
any alternate method for executing this query without inner join — dude, Aug 01 '12 at 06: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 https://www.postgresql.org/docs/8.2/sql-delete.html — icc97, Mar 10 '21 at 07:00
8.2 is **not** the ["current" documentation](https://www.postgresql.org/docs/current/sql-delete.html). 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 — , Mar 10 '21 at 07:13

Omesh · Answer 1 · 2012-08-01T07:01:51.787

446

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 01 '12 at 07:01

answered Aug 01 '12 at 06:46

Omesh

27,801
6
42
51

@0mesh its for mysql .. my doubt is for sql and postgre sql – dude Aug 01 '12 at 06:51
31

for larger tables, the first solution in this answer is potentially much faster. – mgoldwasser Nov 02 '14 at 16:58
2

The best answer, especially the first one, as it allows you to match by multiple fields. – Kostanos Nov 14 '17 at 14:58
the second one will work across all databases – icc97 Oct 07 '22 at 08:05

score 74 · Answer 2 · answered Dec 14 '18 at 00:51

74

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 '18 at 00:51

Mahesh

3,727
1
39
49

1

The `a.id = b.id` part is basically the join. I overlooked this, but it's the key piece, thank you! – Adam Hughes Oct 06 '22 at 13:47

score 70 · Accepted Answer · edited Jun 12 '13 at 07:37

70

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 '13 at 07:37

fthiella

48,073
15
90
106

answered Aug 01 '12 at 07:01

dude

4,532
8
33
51

1

Yes this is what postgres recommend as the 'standard' way to do it in the [delete documentation notes](https://www.postgresql.org/docs/current/sql-delete.html#id-1.9.3.100.8): "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 07 '22 at 08:09

score 55 · Answer 4 · edited Feb 26 '16 at 18:45

55

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 '16 at 18:45

Community

1
1

answered Jul 30 '15 at 18:41

Robert Casey

1,511
18
19

1

CTEs maybe 9.1+ but `USING` was allowed in 8.2 as well https://www.postgresql.org/docs/8.2/sql-delete.html – icc97 Mar 10 '21 at 07:06

score 32 · Answer 5 · edited Mar 13 '17 at 16:11

32

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 '17 at 16:11

Alexander Farber

21,519
75
241
416

answered Feb 11 '16 at 16:04

Ivan Rave

1,759
18
15

icc97 · Answer 6 · 2023-02-09T10:40:27.227

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.

score 0 · Answer 7 · answered Jun 02 '23 at 05:22

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

****

PostgreSQL delete with inner join

7 Answers7

Notes

Linked

Related