(My)SQL - Populate values in Table A based on Table B

Question

My apologies - I realise this is a pretty common question but I'm having some trouble getting my head around the query I need to use.

I'm essentially trying to copy values from one table to another table, based on querying the second table.

For example, I have those two tables:

+-----------------------------------------------------+
|                       Table 1                       |
+----------+------+----------+------------+-----------+
| UniqueID | name | location | Flavour    | Status    |
| 723948   |      |          | Mango      | Tried     |
| 723948   |      |          | Orange     | Not tried |
| 723948   |      |          | Strawberry | Tried     |
| 2346     |      |          | Mango      | Not tried |
| 2346     |      |          | Strawberry | Tried     |
| 3745     |      |          | Strawberry | Tried     |
| 3745     |      |          | Mango      | Tried     |
+----------+------+----------+------------+-----------+

and

+-------------------------------------------+
|                  Table 2                  |
+----------+-----------------+--------------+
| UniqueID | fullname        | baselocation |
| 723948   | Steve Stevenson | London       |
| 2346     | Mary Marington  | New York     |
+----------+-----------------+--------------+

I then want the name and location columns in table 1 to be populated based on the UniqueID matching with table 2, so the result would be as follows:

+--------------------------------------------------------------------------------+
|                              Desired result (Table 1)                          |
+--------------------------+-----------------+----------+------------+-----------+
| UniqueID                 | name            | location | Flavour    | Status    |
| 723948                   | Steve Stevenson | London   | Mango      | Tried     |
| 723948                   | Steve Stevenson | London   | Orange     | Not tried |
| 723948                   | Steve Stevenson | London   | Strawberry | Tried     |
| 2346                     | Mary Marington  | New York | Mango      | Not tried |
| 2346                     | Mary Marington  | New York | Strawberry | Tried     |
| 3745                     |                 |          | Strawberry | Tried     |
| 3745                     |                 |          | Mango      | Tried     |
+--------------------------+-----------------+----------+------------+-----------+

I know I should be using something within the lines of UPDATE, SET, FROM then JOIN but I'm not 100% sure what the correct and most accurate/efficient query would be.

Thanks!

Possible duplicate of [MySQL - UPDATE query based on SELECT Query](http://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query) — Robert, Jan 18 '16 at 19:33
Will you be throwing away the original tables? If not, then perhaps you should do the `JOINing` at `SELECT` time. — Rick James, Jan 18 '16 at 20:21

Michael Berkowski · Accepted Answer · 2016-01-18T19:29:36.270

You are looking for an UPDATE statement with a pretty conventional INNER JOIN, using MySQL's multi-table UPDATE syntax.

The basic format is:

UPDATE
  T1
  JOIN T2 ON T1.col = T2.col
  JOIN T3 ON T2.other = T3.other
  ...
SET
  T1.updatedcol = T2.colvalue,
  T1.updatedcol2 = T2.colvalue2,

In your case:

UPDATE 
  Table1
  INNER JOIN Table2 ON Table1.UniqueID = Table2.UniqueID
SET
  -- Set values in Table1 from joined rows in Table2
  Table1.name = Table2.fullname,
  Table1.location = Table2.baselocation

Only matched rows will be modified, which is why you can do this with an INNER JOIN rather than requiring a LEFT JOIN.

(Here is a demonstration, though SQLfiddle is failing to run SELECT * FROM Table1 at the moment)

score 0 · Answer 2 · edited Jan 18 '16 at 20:26

0

Try using the following query,

UPDATE `table 1` AS t1
INNER JOIN `table 2` AS t2
ON t1.UniqueID = t2.UniqueID
SET
  name = fullname,
  location=baselocation

edited Jan 18 '16 at 20:26

Abubakr Dar

4,078
4
22
28

answered Jan 18 '16 at 19:28

Robert

2,342
2
24
41

(My)SQL - Populate values in Table A based on Table B

2 Answers2