Error PostgreSQL delete with 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 following error:

ERROR: syntax error at >>INNER<< LINE 1: DELETE FROM processing_transaction AS pt INNER JOIN processi...

Please could you help me to find the error in my SQL-query

---

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

BEGIN WORK;

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

ROLLBACK;

But I need to delete data from two tables (processing_transaction and processing_transaction_movement) , I have looking for somethink like that and found out that I can do it with "ON DELETE CASCADE". But I don´t know how to use this here. So Please help me again.

Solution 1

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

Solution 2

As far as I can tell, JOIN syntax isn't supported in DELETE statements. According to the documentation for DELETE, you can use certain kinds of other sub-queries, though; the USING syntax in particular might be interesting. There are a few examples on that page for DELETE queries that look at other tables.

Comments

[Grant Birchmeier]

I used this example to form my own query, and it took the ; as the end of the statement and deleted the whole table. That sucked. Why is that ; there?

[Desislav Kamenov]

You are right, it was a typo and i removed the ; from the query.

[Momer]

As a general rule, you shouldn't copy/paste code for this reason. Additionally, you can wrap your query in BEGIN and roll it back or commit it optionally: postgresql.org/docs/9.3/static/sql-begin.html