1

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 
    );
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Sandwick
  • 21
  • 4

1 Answers1

2

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

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;

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

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?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • The prospect database has some information, but not all. Prospects has the following fields "prospect" and "accounts". Company has "entry_company_tax_id" and I would like to update company.entry_company_tax_id with prospects.accounts when prospects.prospect = company.entry_company_tax_id. so will the query above work ? – Sandwick Aug 08 '17 at 11:50
  • @Sandwick . . . The link between the tables needs to be correct. If you are getting all `NULL`s now, you are probably using the wrong columns. – Gordon Linoff Aug 08 '17 at 12:01