How to solve delete using inner join in sql database?

Question

I get the following error trying to delete records:

ERROR: syntax error at or near "SPO"
LINE 1: DELETE SPO,SP
^

This is my code:

DELETE SPO,SP 
FROM stock_pack_operation SPO  
INNER JOIN stock_picking SP ON sp.id = spo.picking_id  
WHERE sp.company_id = 1;

Answer 1

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;