Delete Query difference between MySQL and PostgreSQL

Question

I'm making a switch from MySQL to PostgreSQL and this delete query that on mysql shows an error in postgresql:

DELETE c FROM customers_vs_coupons c JOIN coupons d ON c.coupon_id=d.id WHERE d.company_id = ?1



ERROR:  syntax error at or near "c"
LINE 1: DELETE c FROM customers_vs_coupons c JOIN coupons d ON c.cou...
               ^
SQL state: 42601
Character: 8

i can get it to work by changing the query to

DELETE FROM customers_vs_coupons USING customers_vs_coupons AS c JOIN coupons AS d ON c.coupon_id=d.id WHERE d.company_id = 1;

but would like to know why its throwing the error?

Answer 1

It is throwing an error because MySQL and PostgreSQL are not the same thing. Neither product promises to regurgitate all the non-standard behaviors of the other (nor for that matter promises to implement all of the standard).

Note that your "working" query is wrong. It will delete all rows, because you do a 3-way join and there is no restriction between the table being deleted from and the other two tables.

The correct PostgreSQL syntax would be:

DELETE FROM customers_vs_coupons c USING coupons AS d where c.coupon_id=d.id and d.company_id = 1;

Or this syntax works with both databases:

DELETE FROM customers_vs_coupons c where exists (select 1 from coupons AS d where c.coupon_id=d.id and d.company_id = 1);