Inner join on delete statement with apache derby

Question

I have used the following syntax to select the needed row's:

select * from Customer_Coupon inner join Company_Coupon on
Customer_Coupon.COUPON_Id =Company_Coupon.COUPON_Id where COMP_Id = 123;

What syntax should I use to delete the same row's?

Answer 1

You can use the DELETE statment with an IN operator:

DELETE FROM Customer_Coupon WHERE COUPON_ID IN (
   SELECT COUPON_ID FROM Customer_Coupon INNER JOIN Company_Coupon ON
   Customer_Coupon.COUPON_Id =Company_Coupon.COUPON_Id WHERE COMP_Id = 123
);

This will delete all rows in the Customer_Coupon table where the COUPON_ID is returned by the inner SELECT.