MySQL: Update column of multiple rows based on another row

Question

I have a database of Tweets with the following columns

+------------------------+--------------+
| Field                  | Type         | 
+------------------------+--------------+
| id                     | varchar(128) |
| message_id             | bigint(20)   |
| message                | text         |
| from_id                | int(12)      |
| cnty                   | int(5)       |
| county                 | varchar(64)  |
| city                   | varchar(64)  |
| state                  | varchar(2)   |
+------------------------+--------------+

The database contains multiple messages for each distinct from_id. There is one row for each from_id that contains cnty, county, city. and state and all other rows with the same from_id have NULL values for those columns. I would like to fill them in with the same values for each from_id. Is this possible in MySQL? My first thought was to write a Python script to do this, but maybe that is overkill.

Having so many duplicate values is very inefficient. If you can, you should revisit your db design. Anyway, you can get the data from the first row and use a regular sql query to update all rows with that from_id. — Sterls
– Sterls, Commented Sep 11, 2015 at 21:15

Gordon Linoff · Accepted Answer · 2015-09-11 21:16:39Z

2

You could use a subquery that gets all non null values, then use an UPDATE query with a join:

UPDATE
  tweets INNER JOIN (
    SELECT from_id, MAX(cnty) AS cnty, MAX(country) AS country, MAX(city) AS city, ...
    FROM tweets
    GROUP BY from_id) m
  ON tweets.from_id = m.from_id
SET
  tweets.cnty=m.cnty,
  tweets.country=m.country,
  tweets.city=m.city,
  ...

edited Sep 11, 2015 at 21:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

answered Sep 11, 2015 at 21:14

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

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

Collectives™ on Stack Overflow

MySQL: Update column of multiple rows based on another row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related