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.
1 Postgres - DELETE JOIN, USING giving error 1 delete record from select by composite key 1 Delete rows where date is not minimum 0 Insert records from one table to another and then delete inserted records
And please read the manual: there is no INNER JOINavailable for the DELETE statement: postgresql.org/docs/8.2/static/sql-delete.html – a_horse_with_no_name Aug 1 '12 at 6:53 any alternate method for executing this query without inner join
And please read the manual: there is no INNER JOINavailable for the DELETE statement: postgresql.org/docs/8.2/static/sql-delete.html – a_horse_with_no_name
The SQL standard does not include any option to "join" tables in a DELETE statement. So Postgres' USINGoption is just as non-standard as the JOINoption MySQL and SQL Server use. Neither of them defines the "standard" and e.g. Oracle and DB2 have not option at all to "join" other tables
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With