Update MySql Field (if field is not empty, go to next one)

Question

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

score 4 · Accepted Answer · edited May 23 '17 at 12:07

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)

thank you for your answer, i need to read about the case statement ... to learn it ... the answer below is working — farkas norbert, Jun 24 '12 at 12:18
this will not help OP because if col2 & col3 is NULL, OP want to update only col2 data. Col3 data should be NULL. — Fahim Parkar, Jun 24 '12 at 12:18
@farkasnorbert : Which answer is working?? Please accept that answer if it is working. See [here, how to accept answer](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) — Fahim Parkar, Jun 24 '12 at 12:18

score 1 · Answer 2 · edited Jun 24 '12 at 11:02

1

You could do something like

UPDATE table SET columnC = IF(columnB is not null, "value", columnC) where id = 1

edited Jun 24 '12 at 11:02

Fahim Parkar

30,974
45
160
276

answered Jun 24 '12 at 10:38

user1474090

675
6
5

@farkasnorbert : Means you will write same query to update columnD?? `UPDATE table SET columnD = IF(columnC is not null, "value", columnD) where id = 1` – Fahim Parkar Jun 24 '12 at 12:21

Samson · Answer 3 · 2012-06-24T11:10:42.613

0

You can try something like this:

    $result=mysql_query('SELECT fav1, fav2, fav3, fav4, fav5 FROM users where id=".."');

    $row = mysql_fetch_array($result);

      foreach ($row as $columnName=>$value)
      {
         if ($value==null)
            {
             //update users set $columnName=pageID 
             break;
            }
      }

edited Jun 24 '12 at 11:10

answered Jun 24 '12 at 10:14

Samson

2,801
7
37
55

thanks for your answers, something is not working ... let be more specific...what i try to make is an "Add to favourite" script ... i have a table with users ... the table its look like this (ID, UserName, Pasword, Favourite1 Favourite2 Favourite3 Favourite4 Favourite5) ... on every page i have a submit button ... so every user can add 5 favourite pages ... in his account ... so if Favourite1 is not empty go to Favourite2 and store the page_id there ... i have the imput form and is working...I made everything..but i don`t know how to make ...to store in Favourite2 if Favourite1 is not empty – farkas norbert Jun 24 '12 at 10:50

Update MySql Field (if field is not empty, go to next one)

3 Answers3

Linked