CODEWITHSUNDEEP
mysql
Jordash
Jordash

Reputation: 3093

MySQL update table with data from another table based on field

I have a database that looks like this

 leads
 name         zip          city      county     state
 Bill         84058
 Susan        90001

 FullUSZipCodes
 ZipCode       City        County       State
 84058         Orem        Utah         Utah
 90001         Los Angeles South        California

As you can see, in the leads database the city, count and state are empty, i'm trying to merge the data so that the first table would look like this:

 leads
 name         zip          city      county     state
 Bill         84058        Orem      Utah       Utah
 Susan        90001        Los Angeles South    California

This is the first query I tried:

UPDATE leads SET leads.county = FullUSZipCodes.County WHERE leads.zip = FullUSZipCodes.ZipCode

which didn't work, and here is the 2nd query:

UPDATE leads INNER JOIN FullUSZipCodes ON leads.zip = FullUSZipCodes.ZipCode SET leads.county = FullUSZipCodes.County, leads.city = FullUSZipCodes.City, leads.state = FullUSZipCodes.State

Upvotes: 1

Views: 25

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

Your second query looks like it should work, but I would write it slightly differently:

UPDATE leads t1
INNER JOIN FullUSZipCodes t2
    ON t1.zip = t2.ZipCode
SET
    t1.city = t2.City,
    t1.county = t2.County,
    t1.state = t2.State
WHERE
    t1.city IS NULL OR t1.county IS NULL OR t1.state IS NULL;

If the leads table has a lot of records, and performance is a concern, you can try adding a WHERE clause to the update query which targets only those records which are missing one or more pieces of address information. I think there is nothing wrong with always updating all three columns together, because typically this information tends to be grouped together.

Upvotes: 1

Related Questions