MySql 从另一张表更新一张表失败 [英] MySql Update one table from another fails

问题描述

我试图从另一个表更新一个表，并且查询将所有字段customers.entry_company_tax_id 设置为NULL"，这对我来说没有意义.

I was trying to update one table from another table and the query set all of the fields,customers.entry_company_tax_id, to "NULL" which did not make sense to me.

这是查询，有人能告诉我我做错了什么吗?

here is the query, can someone tell me what I did wrong ?

UPDATE customers 
    SET customers.entry_company_tax_id = (
        SELECT prospects.account
        FROM prospects
        WHERE prospects.prospect  = customers.entry_company_tax_id 
    );

推荐答案

如果没有匹配项，它们将被设置为 NULL.

They would be set to NULL if there are no matches.

我将首先使用 JOIN 语法进行更新:

I would start by using JOIN syntax for the update:

UPDATE customers c JOIN
       prospects p
       ON p.prospect = c.entry_company_tax_id
    SET c.entry_company_tax_id = p.account;

这样做的好处是它只会更新匹配的记录——所以没有新的 NULL 值(除非 p.account 是 NULL).

This has the advantage that it will only update matching records -- so no new NULL values (unless p.account is NULL).

然后，您可以调查这是否是两个表的正确 JOIN 键.您是否使用 entry_company_tax_id 用于 JOIN 和字段值?

Then, you can investigate if that is the right JOIN key for the two tables. Are you use entry_company_tax_id is used both for the JOIN and for the field value?

这篇关于MySql 从另一张表更新一张表失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！