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. — user554546
– user554546, Commented Nov 13, 2012 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
– HLGEM, Commented Nov 13, 2012 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
– Desislav Kamenov, Commented Nov 15, 2012 at 8:01

Desislav Kamenov · Accepted Answer · 2014-03-22 18:54:57Z

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, 2014 at 18:54

answered Nov 13, 2012 at 16:49

Desislav Kamenov

1,2037 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Grant Birchmeier Over a year ago

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 Over a year ago

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

Momer Over a year ago

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

Jan Krüger · Accepted Answer · 2012-11-13 16:47:01Z

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.

answered Nov 13, 2012 at 16:47

Jan Krüger

18.6k3 gold badges64 silver badges51 bronze badges

Collectives™ on Stack Overflow

Error PostgreSQL delete with INNER JOIN

Postgres 8.4

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Postgres 8.4

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related