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.

Ummmm...your `delete` statement is nonsensical, as you haven't specified the table from which you'd like to delete rows. — , Nov 13 '12 at 16:47
I don;t know postgres but in SQl Server you shoudl put the alias for the table you are deleting from after the word delete. It doesn't know which table to delete from. — HLGEM, Nov 13 '12 at 16:47
If you have foreign keys on the other tables when you delete from the main table it will automatically cascade and delete records. — Desislav Kamenov, Nov 15 '12 at 08:01

Desislav Kamenov · Accepted Answer · 2014-03-22T18:54:57.903

16

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

edited Mar 22 '14 at 18:54

answered Nov 13 '12 at 16:49

Desislav Kamenov

1,193
6
13

1

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? – Grant Birchmeier Nov 22 '13 at 21:12
You are right, it was a typo and i removed the ; from the query. – Desislav Kamenov Mar 22 '14 at 18:55
2

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: http://www.postgresql.org/docs/9.3/static/sql-begin.html – Momer Dec 17 '14 at 18:34

score 2 · Answer 2 · answered Nov 13 '12 at 16:47

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.

Error PostgreSQL delete with INNER JOIN

Postgres 8.4

2 Answers2