Jump to content

Update values from col1 to col2


jazzman1

Recommended Posts

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.

 

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.