PostgreSQL delete with inner join

Can we use delete with inner JOIN?

MySQL DELETE JOIN with INNER JOINMySQL also allows you to use the INNER JOIN clause in the DELETE statement to delete rows from a table and the matching rows in another table.

Can we use JOIN IN delete query in PostgreSQL?

PostgreSQL doesn't support the DELETE JOIN statement. However, it does support the USING clause in the DELETE statement that provides similar functionality as the DELETE JOIN . In this syntax: First, specify the table expression after the USING keyword.

How do I delete a PostgreSQL entry?

First, specify the table from which you want to delete data in the DELETE FROM clause. Second, specify which rows to delete by using the condition in the WHERE clause. The WHERE clause is optional. However, if you omit it, the DELETE statement will delete all rows in the table.

How does inner JOIN work in PostgreSQL?

In PostgreSQL the INNER JOIN keyword selects all rows from both the tables as long as the condition satisfies. This keyword will create the result-set by combining all rows from both the tables where the condition satisfies i.e value of the common field will be the same. Syntax: SELECT table1.

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

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

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

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

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

PostgreSQL delete with inner join

Tags:

sql

postgresql

People also ask

Recent Activity

Donate For Us

PostgreSQL delete with inner join

Tags:

sql

postgresql

People also ask

Related questions

Recent Activity

Donate For Us