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.
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.
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.
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,
IN
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With