MySQL insert from select query

Question

I have two tables:

TABLE1

| ColumnA  | ColumnB  |
-----------------------
| 123      | 1        |

TABLE2

| ColumnA  | ColumnC  |
-----------------------
| 123      | 20       |

I altered Table 2 to add a new column, so it looks like this

NEW TABLE2

| ColumnA  | ColumnC  | ColumnB  |
----------------------------------
| 123      | 20       | NULL     |

Now, I want to select the values of TABLE1.Column B and insert it into TABLE2.Column B

So I wrote this query:

INSERT INTO TABLE2 (ColumnB)
SELECT t1.ColumnB
FROM TABLE1 AS t1
INNER JOIN
TABLE2 AS t2
ON t1.ColumnA = t2.ColumnA;

But it doesn't seem to do the trick. Am I missing something?

Answer 1

You need to use an UPDATE statement to update the table. An INSERT will add new rows.

UPDATE Table2
JOIN Table1
ON
Table2.columnA = Table1.ColumnA
SET 
Table2.ColumnB = Table1.ColumnB
WHERE
Table2.ColumnB IS NULL

Answer 2

You don't appear to want to insert. It appears you want to update the rows already in table2. You can use:

update table2 set ColumbB=(select ColumnB from table1 where table1.ColumnA=table2.ColumnA)