tooba tooba - 1 year ago 106
SQL Question

update multiple columns with condition

I have 4 columns. I am trying to update only that values of the columns which is like(equal) to the value come in loop.

for ($a=0; $a<$i ; $a++) {
$sql= "update table1 set
col1= CASE when col1 like '$distval[$a]' then col1='$arr[$a]' else col1 end,
col2=CASE when col2 like '$distval[$a]' then col2='$arr[$a]' else col2 end,
col3=CASE when col3 like '$distval[$a]' then col3='$arr[$a]' else col3 end,
col4=CASE when col4 like '$distval[$a]' then col4='$arr[$a]' else col4 end";
mysqli_query($conn, $sql);

Let me explain the code:

$distval: is the array which contains the distinct values of all the four columns which i want to update with values entered by the users.

$arr: is another array of the text fields of same number as '$distval'. Now the value enter in the first text field should update all the values in table like(equals to) first value of '$distvalue'.

The code doesn't update anything without any error.

Answer Source

remove the colX='$arr[$a]' inside the then you need only '$arr[$a]'

$sql= "update table1 set 
          col1= CASE when col1 like '%". $distval[$a]. "%' then '$arr[$a]' else col1 end,
          col2= CASE when col2 like '%". $distval[$a]. "%' then '$arr[$a]' else col2 end,
          col3= CASE when col3 like '%". $distval[$a]. "%' then '$arr[$a]' else col3 end, 
          col4= CASE when col4 like '%". $distval[$a]. "%' then '$arr[$a]' else col4 end";
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download