Update only one column (next column) if previous column is empty

Question

I have created table and inserted values as below.

create table mytable (id INT, col1 INT, col2 INT, col3 INT);

insert into mytable values
(1,1,1,NULL),
(2,1,NULL,NULL);

What I want to do is update col2 if col1 is not null, update col3 if col2 is not null and so on... BUT only one column to update.

Consider I want to update data for id=2, then only col2 should be updated and not col2, col3 as both are null.

When I tried with below query, then all columns get updated.

update myTable set  
      col1 = ( IF (col1 is null, 9, col1) ),
      col2 = ( IF (col2 is null, 9, col2) ),
      col3 = ( IF (col3 is null, 9, col3) );

What should be done so that only one column gets updated.

Your code updates every nul column.. Place the condition on the column before! — Samson, Jun 24 '12 at 12:52
On this damn phone I can t edit comments. For col2 you need to check if col1 is not null before you update, for col3 check col1 amd col 2 and so on — Samson, Jun 24 '12 at 13:13

saluce · Answer 1 · 2012-06-26T00:16:13.620

0

You can do it in two distinct passes, a serial update fashion:

UPDATE mytable SET col3 = 9 WHERE col2 IS NOT NULL;
UPDATE mytable SET col2 = 9 WHERE col1 IS NOT NULL;

Doing it in this order prevents the cascading of updates across all columns.

edited Jun 26 '12 at 00:16

answered Jun 24 '12 at 12:55

saluce

13,035
3
50
67

@Saluce The second actually leaves the `col2` to be null and fills `col3`, which is not as expected by the OP. OP wants *update col2 if col1 is not null, update col3 if col2 is not null and so on.* – Ravinder Reddy Jun 25 '12 at 09:16
@Saluce For the required solution you should do updates in reverse order of columns. – Ravinder Reddy Jun 25 '12 at 09:23
@saluce No. this update too won't work. It leaves `col2` as `null` and updates `col3` which is not as required. Apart from this, if there are large (`n`) number of columns, then `colN=...`, `colN-1`, ... would be too long, lacks readability and I doubt its performance too. – Ravinder Reddy Jun 25 '12 at 23:32
@FahimParkar This solution is not correct to update the just next NULL field, say col3, if a field in sequential order, say col2, is not null. – Ravinder Reddy Jun 25 '12 at 23:35
@saluce Just saw your update. Though updated solutions is not a single query, removed my down vote. :) – Ravinder Reddy Jun 27 '12 at 06:25

score 0 · Accepted Answer · edited May 23 '17 at 12:15

It seems your query is an extension to Update MySql Field (if field is not empty, go to next one)

You need to set column values checking in the reverse order.

update table_name set  
--      more next ( 4 to n ) columns here if required       
    col3 = ( case when ( col2 is not null and col3 is null ) then 7 else col3 end )  
    , col2 = ( case when ( col1 is not null and col2 is null ) then 8 else col2 end )  
    , col1 = ( case when ( col1 is null ) then 9 else col1 end )  
;

Example Table:

mysql> create table col_values ( id INT, col1 INT, col2 INT, col3 INT );
Query OK, 0 rows affected (0.06 sec)

mysql> insert into col_values values ( 1, 1, 1, NULL ), ( 2, 1, NULL, NULL ), ( 3, NULL, NULL, NULL );
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from col_values;
+------+------+------+------+
| id   | col1 | col2 | col3 |
+------+------+------+------+
|    1 |    1 |    1 | NULL |
|    2 |    1 | NULL | NULL |
|    3 | NULL | NULL | NULL |
+------+------+------+------+
3 rows in set (0.00 sec)

mysql> update col_values set
    ->     col3 = ( case when ( col2 is not NULL and col3 is NULL ) then 7 else col3 end )
    ->     , col2 = ( case when ( col1 is not NULL and col2 is NULL ) then 8 else col2 end )
    ->     , col1 = ( case when ( col1 is NULL ) then 9 else col1 end )
    -> ;
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from col_values;
+------+------+------+------+
| id   | col1 | col2 | col3 |
+------+------+------+------+
|    1 |    1 |    1 |    7 |
|    2 |    1 |    8 | NULL |
|    3 |    9 | NULL | NULL |
+------+------+------+------+
3 rows in set (0.00 sec)

You can also use if function as an alternative to case statement.

mysql> truncate table col_values;
Query OK, 3 rows affected (0.01 sec)

mysql> insert into col_values values ( 1, 1, 1, NULL ), ( 2, 1, NULL, NULL ), ( 3, NULL, NULL, NULL );
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from col_values;
+------+------+------+------+
| id   | col1 | col2 | col3 |
+------+------+------+------+
|    1 |    1 |    1 | NULL |
|    2 |    1 | NULL | NULL |
|    3 | NULL | NULL | NULL |
+------+------+------+------+
3 rows in set (0.00 sec)

mysql> update col_values set
    ->     col3 = if( ( col2 is not NULL and col3 is NULL ), 7, col3 )
    ->     , col2 = if( ( col1 is not NULL and col2 is NULL ), 8, col2 )
    ->     , col1 = if( col1 is NULL, 9, col1 )
    -> ;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from col_values;
+------+------+------+------+
| id   | col1 | col2 | col3 |
+------+------+------+------+
|    1 |    1 |    1 |    7 |
|    2 |    1 |    8 | NULL |
|    3 |    9 | NULL | NULL |
+------+------+------+------+
3 rows in set (0.00 sec)

Update only one column (next column) if previous column is empty

2 Answers2

Linked