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.
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 BEGIN
ing a transaction and check the results if they are what you expect before COMMIT
ting 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