Task:

Let’s suppose you have Table1 with a million rows and you just altered the table by adding a new column. This new column is empty (NULL) in Table1 but you want to update it with the values that are in a column in Table2.

Solution:

First, do not ruin your day, create a backup table with the same structure and data as TABLE1 by running below query:

CREATE TABLE TABLE1_BKP AS SELECT * FROM TABLE1;

Then test the update query by running below query:

UPDATE TABLE1_BKP A SET
A.COLUMN_TO_UPDATE = (SELECT B.COLUMN_WITH_DATA FROM TABLE2 B WHERE B.COLUMN_WITH_DATA IS NOT NULL AND B.ID = A.ID)
WHERE A.ID IN (SELECT B.ID FROM TABLE2 B WHERE B.ID = A.ID);

If the update went well, run the same query again but replace TABLE1_BKP with TABLE1.

It goes without saying that any UPDATE and DELETE queries must have a WHERE clause to limit the result as much as possible, in case something unexpected happens. And that’s why you need a backup table and test on it first.