Update values from col1 to col2

[jazzman1]

Friends, I have the following table:

SELECT * FROM tbl1;

+----+-------+-------+
| id | col_1 | col_2 |
+----+-------+-------+
|  1 |     1 |  NULL |
|  2 |     2 |     2 |
|  3 |  NULL |     3 |
|  4 |     4 |  NULL |
+----+-------+-------+
4 ROWS IN SET (0.00 sec)

So, if I wanna update only the null values of col_2 with these from col_1 or vice versa I'm running the following update statement with mysql ifnull() func.

mysql> UPDATE test.tbl1 t1 SET t1.col_2 = ifnull(t1.col_2, t1.col_1), t1.col_1= ifnull(t1.col_1, t1.col_2);

Query OK, 3 ROWS affected (0.06 sec)
ROWS matched: 4  Changed: 3  Warnings: 0

Is there a way to use LEFT JOIN to get all null values from every columns and rows, then to update the columns in the same way? I notice that the sub-query failed for me within an update statement.

 

[Barand]

Are the values in col1 and col2 always the same, unless NULL, or is that just a coincidence in your sample data?

 

If they will become identical you only need to update null values in col1, as col2 will become redundant after the update.

UPDATE jazz
SET col1 = col2
WHERE col1 IS NULL;

Now you can drop col2.

[jazzman1]

 

Are the values in col1 and col2 always the same, unless NULL, or is that just a coincidence in your sample data?

 

No. They are not the same only the column data type. It was just a coincidence.

[Barand]

I could not see any value in using a join. You would need either two queries or conditional statements as now. I created a table with 1000 rows of random values and nulls and ran your query. I also ran your query a second time on the same data with an added "WHERE col_1 IS NULL OR col_2 is NULL".

 

Here are the the two run times

1. 0.05800
2. 0.02900

[Barand]

Disregard that last post. I ran it few more times and the WHERE made no difference, in fact it was slower on occasions

[jazzman1]

Disregard that last post. I ran it few more times and the WHERE made no difference, in fact it was slower on occasions

Thanks anyway, Barry! it's not an important thing.