mysql字段为空时不更新_更新MySql字段(如果字段不为空，则转到下一个字段)

您必须使用case仅更新选择列。

例：

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 = ...

;使用mysql prepare来替换查询参数。

另请参阅Quassnoi's answer to a similar posting。

更新：

IF ColumnB is not empty then update ColumnC, if ColumnC is not empty update ColumnD ...until it finds an empty column.

您需要以相反的顺序设置列值检查。

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 )

;示例表：

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)您还可以使用if函数替代case语句。

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)