Postgresql Delete with Inner Join

PostgreSQL delete with inner join

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

Error PostgreSQL delete with INNER JOIN

You can't use JOIN in DELETE statement. Instead use USING and put the second table there.
Something like this should work (sorry but i can't test it, so run it after BEGINing a transaction and check the results if they are what you expect before COMMITting it; ROLLBACK if they aren't).

DELETE
FROM processing_transaction AS pt 
USING processing_transaction_movement AS ptm, test_package tesp, test_batch tbat 
WHERE pt.processing_transaction_type = 'TEST'
AND pt.processing_transaction_id = ptm.processing_transaction_id
AND pt.test_package_id = tesp.test_package_id
AND tesp.test_batch_id = tbat.test_batch_id

Here is a link to the documentation.
http://www.postgresql.org/docs/current/static/sql-delete.html

How to solve delete using inner join in sql database?

You cannot join in the FROM of DELETEs in Postgres.

If you want to delete all the row from stock_picking sp with a company_id of 1 and all the corresponding rows from stock_pack_operation, you can use a cte with a RETURNING clause.

WITH cte
(
DELETE FROM stock_picking sp
       WHERE sp.company_id = 1
       RETURNING sp.id
)
DELETE FROM stock_pack_operation spo
       USING cte
       WHERE spo.picking_id = cte.id;

If want to delete all the rows from stock_pack_operation where the picking_id is one the ids from stock_picking for the company of ID 1 you can use USING.

DELETE FROM stock_pack_operation spo
       USING stock_picking sp
       WHERE spo.picking_id = sp.id
             AND sp.company_id = 1;

Delete records from multiple tables using Join with PostgreSQL

Instead, delete from one table at a time:

with t1 as (
      delete t1 
      where t1.col = 'something'
      returning *
     ),
     t3 as (
      delete t3
      where t3.t1_id in (select id from t1)
      returning *
     )
delete t2
where t2.id in (select t2_id from t3);

This is not exactly the same. The inner join requires matches among the tables. But I think this is your intention.

PostgreSQL delete rows that outer join from multiple tables

Simply use not exists:

delete from tableA a
    where not exists (select 1 from tableB b where b.idB = a.idB) or
          not exists (select 1 from tableC c where c.idC = a.idC);

---

Related Topics