CODEWITHSUNDEEP
phpmysqlsqlphpmyadmin
Nicholas Summers
Nicholas Summers

Reputation: 4756

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)

Upvotes: 1

Views: 83

Answers (4)

JDro04
JDro04

Reputation: 670

I wouldn't do this.

https://en.wikipedia.org/wiki/List_of_ZIP_code_prefixes

The first 3 digits of a zip code indicate the state it is in. I'd do some logic based on that.

Edit: This doesn't have to be too complex

UPDATE CUSTOMERS
SET STATE = CASE
                WHEN SUBSTRING(zip, 1,3) IN ('448','449', ect....) THEN 'OH'
                ....
                ....
            END
WHERE STATE IS NULL

Upvotes: 1

MonkeyZeus
MonkeyZeus

Reputation: 20737

Not sure about performance but you should be able to:

update table_name tn1
   set tn1.state = (select max(tn2.state) from table_name tn2 where tn2.zip = tn1.zip and tn2.state is not null)
 where tn1.state is null

Upvotes: 0

Philipp
Philipp

Reputation: 2796

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)

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

you can use update and inner join 

update my_table as a
inner join source_table as b on  a.zip = b.zip 
set a.state = b.state 
where b.state is not null
or b.state = ''
AND country = 'united states' ;

Upvotes: 2

Related Questions