Skip to content
Advertisement

How to run a unique function to calculate and replace a null value in a database?

enter image description here

In the above database, I want to calculate the value for “A2” using the other 3 variables. The premise is that each column has a specific score with respect to each variable and the javascript would sum the answers in a row with a null blank in the A2 slot in this case.

SELECT Name, A1, IFNULL(A2, $f), A3, A4
FROM demo;

<?php
$x=0

if($A1=='C')
    $x+0;
else($A1=='M')
    $x+10;

if($A3=='R')
    $x+10;

if($A4=='S')
    $x+20;
else($A1=='C')
    $x+0;

if($x<=20)
    $f='T'
else($x>20)
    $f='V'

Advertisement

Answer

You can implement the same logic in SQL with a couple of case expression:

select name, a1,
    coalesce(a2, 
        case when (
              case when a1 = 'M' then 10 else 0 end
            + case when a3 = 'R' then 10 else 0 end
            + case when a4 = 'S' then 20 else 0 end
        ) <= 20 then 'T' else 'V' end
    ) a2,
    a3,
    a4
from demo

If you wanted an update statement:

update demo
set a2 = 
    case when (
          case when a1 = 'M' then 10 else 0 end
        + case when a3 = 'R' then 10 else 0 end
        + case when a4 = 'S' then 20 else 0 end
    ) <= 20 then 'T' else 'V' end
where a2 is null

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement