如果上一列为空，则仅更新一列（下一列） [英] Update only one column (next column) if previous column is empty

问题描述

我已经创建了表格和插入值，如下所示。

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);

如果col1不为null，我想要更新col2，如果col2不为null，则更新col3等等...但只有一列要更新。

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.

考虑我想更新id = 2的数据，然后只更新col2而不是col2，col3因为两者都是null。

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.

推荐答案

您的查询似乎是对 更新MySql字段（如果字段不为空，请转到下一个）

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 )  
;

示例表：

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 作为<$ c的替代方案$ c> case statement。

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)

这篇关于如果上一列为空，则仅更新一列（下一列）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！