更新MySql字段(如果字段不为空，请转到下一个) [英] Update MySql Field (if field is not empty, go to next one)

问题描述

你好，我的表有10列(columnA，ColumnB，ColumnC ...)，ColumnA用于ID.

Hello I have table with 10 columns (columnA, ColumnB, ColumnC ...), ColumnA is for ID.

我想以这种方式在php中进行更新:如果ColumnB不为空，则更新ColumnC，如果ColumnC不为空，则更新ColumnD ...直到找到一个空列. >

I want to make an update in php in this way: IF ColumnB is not empty then update ColumnC, if ColumnC is not empty update ColumnD ...until it finds an empty column.

我知道如何更新一个空列，但是我无法确定如何实现IF语句.

I know how to update an empty column but i can`t figurit out how to implement the IF statement.

直到现在我有了这个:

mysql_query("UPDATE tabel_name SET ColumnB = 'example' WHERE ColumnA = '$ID' "); 

谢谢

推荐答案

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

You have to use case to update only selective columns.

示例:

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准备替换查询参数.

Use mysql prepare to replace query parameters.

另请参阅 Quassnoi对类似帖子的回答.

更新:

如果ColumnB不为空，则更新ColumnC，如果ColumnC不为空，则更新ColumnD ...直到找到空列.

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

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函数代替case语句.

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)

这篇关于更新MySql字段(如果字段不为空，请转到下一个)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！