PostgreSQL error removed using INNER JOIN

Question

Postgres 8.4

DELETE FROM processing_transaction AS pt INNER JOIN processing_transaction_movement AS ptm ON pt.processing_transaction_id = ptm.processing_transaction_id LEFT OUTER JOIN test_package tesp ON pt.test_package_id = tesp.test_package_id LEFT OUTER JOIN test_batch tbat On tesp.test_batch_id = tbat.test_batch_id WHERE pt.processing_transaction_type = 'TEST'; 

I get the following error:

ERROR: syntax error with → INNER <LINE 1: DELETE FROM process_transaction AS pt INNER JOIN processi ...

Please help me find an error in my SQL query

---

Thank you for supporting @desislavkamenov @jan. Now I used this:

BEGINNING OF WORK;

REMOVE FROM process_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

ROLLBACK;

But I need to delete data from two tables (processing_transaction and processing_transaction_movement) . So I searched and found out that I can do this with "ON DELETE CASCADE". But I do not know how to use it here. So please help me again.

Answer 1

You cannot use JOIN in a DELETE statement. Instead, use USE and put the second table. Something like this should work (sorry, but I can’t test it, so run it after the BEGIN transaction and check the results if they are what you expect before COMMIT ting it; ROLLBACK if they are not).

 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

Answer 2

As far as I can tell, JOIN syntax is not supported in DELETE . According to the documentation for DELETE , you can use some kinds of other subqueries; USING syntax is particularly interesting. There are several examples on this page for DELETE queries that look at other tables.