mysql update table using data from another table

Question

i have a table structures in my database like this:

/*city*/
+----------+------------+
|    id    |   name     |
|-----------------------|
|    1     | Gotham     |
|    2     | Metropolis |
|    3     | Smallville |
|    4     | Fawcett    |
+----------+------------+   

/*district*/ 
+----------+------------+------------+
|    id    |   name     |   city_id  |
|------------------------------------|
|    1     |     A      |      1     |
|    2     |     B      |      1     |
|    3     |     C      |      2     |
|    4     |     D      |      2     |
|    5     |     E      |      2     |
|    6     |     F      |      3     |
|    7     |     G      |      3     |
|    8     |     H      |      4     |
+----------+------------+------------+

/*distance*/
+----------+-------------+------------------+-------------------------+---------+
|    id    | origin_city | city_destination |  district_destination   |  length |
|---------------------------------------------------------------------|---------|
|    1     |     2       |         2        |            1            |    4    |
|    2     |     3       |         3        |            1            |    5    |
|    3     |     1       |         1        |            2            |    6    |
|    4     |     2       |         2        |            3            |    5    |
|    5     |     4       |         4        |            1            |    8    |
|    6     |     4       |         2        |            4            |    9    |
|    7     |     4       |         3        |            5            |    11   |
|    8     |     1       |         4        |            6            |    13   |
+----------+-------------+------------------+-------------------------+---------+

the table district is connected to city table via city_id foreign key, and the distance table is connected to both city and district table, the problem is if in distance table, there are wrong city_destination data that don't match with the district_destination, i need to fix this, but i don't know how to use the update query for this kind of trouble, to show the wrong city_destination data i used this query:

SELECT a.* FROM distance a, district b WHERE a.district_destination = b.id AND a.city_destination != b.city_id

@Mureinik if the city_destination is same as city_id for district_destination — Idham Choudry
– Idham Choudry, Commented Feb 26, 2017 at 6:06
distance.city_destination is redundant (It's funcionally dependent on district_destination). To ensure data integrity (after fixing wrong entries) you should define a composite foreign key distance(city_destination, district_destination) referencing district(city_id, id). — Paul Spiegel
– Paul Spiegel, Commented Feb 26, 2017 at 6:28

Paul Spiegel · Accepted Answer · 2017-02-26 06:59:53Z

2

First, ditch the old-school comma syntax for the join operation. Use the JOIN keyword and move the join predicates to an ON clause. Write a SELECT query that returns the existing row to be updated (along with the PK, and the new value to be assigned. (Which looks to be as far as you got.)

Assuming that we want to replace the values in the city_destination column of distance table, and seeing that this this column is functionally dependent on district_destination...

Start with a query that returns the rows to be updated.

SELECT ce.id                  AS id
     , ce.district_destination AS district_destination 
     , ce.city_destination    AS old_city_destination
     , ct.city_id             AS new_city_destination        
  FROM distance ce
  JOIN district ct
    ON ct.id = ce.district_destination
   AND NOT ( ct.city_id <=> ce.city_destination )
 ORDER BY ce.id

In MySQL, a multi-table update is pretty straightforward. The syntax is documented in the MySQL Reference Manual.

First, we'll write it as a SELECT, using the previous query as an inline view

SELECT t.id
     , s.new_city_destination
  FROM ( SELECT ce.id                  AS id
              , ce.district_destination AS district_destination 
              , ce.city_destination    AS old_city_destination
              , ct.city_id             AS new_city_destination        
           FROM distance ce
           JOIN district ct
             ON ct.id = ce.district_destination
            AND NOT ( ct.city_id <=> ce.city_destination )
          ORDER BY ce.id
       ) s
  JOIN distance t
    ON t.id = s.id

Then we can convert that to an UPDATE statement. Replace SELECT ... FROM with UPDATE and add a SET clause at the end. (Before the WHERE clause if there was one.)

UPDATE ( SELECT ce.id                  AS id
              , ce.district_destination AS district_destination 
              , ce.city_destination    AS old_city_destination
              , ct.city_id             AS new_city_destination        
           FROM distance ce
           JOIN district ct
             ON ct.id = ce.district_destination
            AND NOT ( ct.city_id <=> ce.city_destination )
          ORDER BY ce.id
       ) s
  JOIN distance t
    ON t.id = s.id
   SET t.city_destination = s.new_city_destination

edited Feb 26, 2017 at 6:59

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

answered Feb 26, 2017 at 5:23

spencer7593

109k15 gold badges122 silver badges148 bronze badges

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

2 Comments

Paul Spiegel Over a year ago

This should work, but there's no need for a subquery. You can also convert the first query to an UPDATE statement: SET ce.city_destination = ct.city_id

spencer7593 Over a year ago

@PaulSpiegel: Yes, valid point. A multitable update can be performed without using an inline view. My main point was that we can first write a SELECT statement. The intent is to show us which rows are going to be updated, and which column values are going to be replaced. Reviewing the results from the SELECT is a good way to verify that our (to be written) UPDATE will be performing the operations we want. Repeating that same SELECT statement in the UPDATE statement (as an inline view) reinforces that idea. If we write an appropriate SELECT statement, we can use that in an UPDATE.

Collectives™ on Stack Overflow

mysql update table using data from another table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related