CODEWITHSUNDEEP
sqlpostgresqlsql-deletecascading-deletes
k1000
k1000

Reputation: 175

Error PostgreSQL delete with INNER JOIN

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.

Upvotes: 6

Views: 14350

Answers (2)

Desislav Kamenov
Desislav Kamenov

Reputation: 1203

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

Upvotes: 16

Jan Kr&#252;ger
Jan Kr&#252;ger

Reputation: 18520

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.

Upvotes: 2

Related Questions