You have to use case
to update only selective columns.
Example:
update table_name set
col1 = ( case when col1 is null then ? else col1 end )
, col2 = ( case when col2 is null then ? else col2 end )
-- , col3 = ...
;
Use mysql prepare to replace query parameters.
Also refer to Quassnoi's answer to a similar posting.
Update:
IF ColumnB is not empty then update ColumnC, if ColumnC is not empty update ColumnD ...until it finds an empty column.
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)