Nick J Nick J - 4 months ago 7
SQL Question

How to copy data from one row to another in SQL

So I have a table named

customers
and in that table there are places where the column
zip
is filled out, but column
state
is not.

We can reasonably assume that if we have another customer with both the
zip
and the
state
column filled, and the
zip
matches a customer without the
state
filled, that the customer without
state
filled can have it filled with the same value as the other matching customer.

The goal is to fill in the missing values when we know them in another row.

Example:

BEFORE: AFTER:
/====================\ /====================\
| id | zip | state | | id | zip | state |
|----|-------|-------| |----|-------|-------|
| 0 | 12345 | FL | ==> | 0 | 12345 | FL |
| 1 | 67890 | CA | ==> | 1 | 67890 | CA |
| 2 | 67890 | | ==> | 2 | 67890 | CA |
| 3 | 12345 | | | 3 | 12345 | FL |
| 4 | 10101 | | | 4 | 10101 | |
\====================/ \====================/


My Question: How can I use MySQL to update the table and fill in the missing data from another row?

So far I have the SQL for finding the missing values:

SELECT *
FROM customers
WHERE country = 'united states'
AND (zip <> '' OR zip IS NOT NULL)
AND (state = '' OR state IS NULL)

Answer

You can do it like this:

UPDATE customers 

INNER JOIN customers AS updateValues ON updateValues.zip = customers.zip AND updateValues.state IS NOT NULL AND updateValues.state != ''

SET customers.state = updateValues.state

WHERE (customers.zip <> '' AND customers.zip IS NOT NULL) 
AND (customers.state = '' OR customers.state IS NULL)

(not sure where WHERE country = 'united states' was coming from, so I removed it)