douxiuyu2028 2012-06-24 09:42
浏览 81
已采纳

更新MySql字段(如果字段不为空,请转到下一个)

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

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.

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

Until now i have this:

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

Thank You

  • 写回答

3条回答 默认 最新

  • douliang2167 2012-06-24 11:32
    关注

    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)
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥30 motoradmin系统的多对多配置
  • ¥15 求组态王串口自定义通信配置方法或代码?
  • ¥15 实验 :UML2.0 结构建模
  • ¥20 用vivado写数字逻辑实验报告撰写,FPGA实验
  • ¥15 为什么shp文件会有这种小方块?
  • ¥15 ecplise在连接数据库时显示加载驱动成功但是数据库连接失败
  • ¥15 visionmaster启动失败,提示为“机器不满足授权而被禁用”
  • ¥15 IDEA中圈复杂度如何具体设置
  • ¥50 labview采集不了数据
  • ¥15 Multisim红外倒车雷达仿真中距离问题