I have an early question and it has been answered where I wanted to update a certain row on the table if same row from another table is updated. its working fine but when that certain row is deleted, the entry stays in the other table. Please help how to delete that also.
Here is the current trigger where account_name is copied from main_accounts to payments table.
UPDATE payments a
JOIN main_accounts b ON a.payment_method = b.payment_method
SET a.account_name = b.account_name
Thank you so much!
You need a Delete with Sub-Query
DELETE FROM payments
WHERE payment_method NOT IN (SELECT payment_method
FROM main_accounts)
But i would suggest you to have On Delete Cascade and On Update Cascade which will make your life easier