Sql-Update字段仅在空ELSE检查下一行时

Question

The following code only updates a field when null for a given ID.

$sql="UPDATE TABLE7 set SCORE1= $high where SCORE1 IS NULL AND ID = 9";

How can I adjust the code so it updates a row only when null, and if not null, checks next row for a certain ID=9 etc. Something like:

$sql="UPDATE TABLE7 set
IF SCORE1 IS NULL THEN SCORE1= $high,
ELSE   //MEANING SCORE1 IS NOT NULL
IF SCORE2 IS NULL THEN SCORE2= $high,
ELSE   //  MEANING SCORE2 IS NOT NULL
IF SCORE3 IS NULL THEN SCORE3= $high
ELSE  //MEANING ALL FIELDS ARE NOT NULL
THROW ERROR (ECHO ‘All FIELDS ARE FILLED’)
WHERE ID = 9"

Thank you.

EDIT

What I want is that after updating the first NULL field, other fields will not be updated.

ie if SCORE1 =NULL, then SCORE1 = $high but SCORE2 and SCORE3 shouldn't update, whether NULL or not.

I only want to update first NULL field ONLY. How can I do that? Thanks.

Answer 1

UPDATE TABLE7 
set SCORE1 = case when SCORE1 IS NULL THEN $high else SCORE1 end,
    SCORE2 = case when SCORE2 IS NULL and SCORE1 is not null THEN $high else SCORE2 end,
    SCORE3 = case when SCORE3 IS NULL and SCORE1 is not null and score2 is not null THEN $high else SCORE3 end
WHERE ID = 9
and (score1 is null or score2 is null or score3 is null)