mysql - 更新行检查重复项

Question

我希望你能帮助我正确的 SQL 语法。

main_accounts (table1)
'---id, group_name, account_name, payment_method

payments (table2)
'---id, account_name, payment_method

我想做的是更新account_name付款行，其中 payment_method等于payment_method在main_accounts表。

我尝试过:

update payments 
set account_name = (select account_name 
                    from main_accounts 
                    where payment_method = payment_method) 
WHERE payment_method = payment_method

但它说:

#1242 - Subquery returns more than 1 row

所以我不知道...我希望我能做到这一点:

update payments 
set account_name = (select account_name 
                    from main_accounts 
                    where payment_method = **{is equal to payment_method in main_accounts table}**

此外，我希望我可以将此作为触发器，当main_accounts时表已更新，account_name自动转到payments table其中account_name当 payment_method 时，将打印到列中匹配payment_method在main_accounts .

Answer 1

您收到该错误，因为您的子查询返回超过 1 个值，该值无法在 SET 语句中使用。在执行 UPDATE 语句时，您应该与其他有问题的表进行 JOIN 操作。类似的东西

UPDATE payments a 
    JOIN main_accounts b ON a.payment_method = b.payment_method        
SET a.account_name = b.account_name;