SQL phpmyadmin更新表，如果为null

Question

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!

Answer 1

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