MySQL update table with data from another table based on field

Question

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

Tim Biegeleisen · Accepted Answer · 2017-11-09 05:46:46Z

1

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.

edited Nov 9, 2017 at 5:46

answered Nov 9, 2017 at 5:45

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jordash Over a year ago

That is the 2nd query I tried, it just kept processing the query but didn't seem to ever finish

Tim Biegeleisen Over a year ago

You can also add a WHERE clause to try and target records which are missing data.

Jordash Over a year ago

About 50,000 records, all of them are missing the data

Jordash Over a year ago

Possibly, it’s been running the query for 20min I️ knew it would take time, didn’t realize it would take that long

Collectives™ on Stack Overflow

MySQL update table with data from another table based on field

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related