Combine SQL UPDATE APPLICATIONS

Question

I am trying to combine these five update statements. I am sure it is simple, but I am new to SQL and cannot understand the logic.

$usqlM1 = "UPDATE tblmaincircles SET mcName= '".$_POST['mcNameM1']."' WHERE mcID='M1';"
$usqlM2 = "UPDATE tblmaincircles SET mcName= '".$_POST['mcNameM2']."' WHERE mcID='M2';";
$usqlM3 = "UPDATE tblmaincircles SET mcName= '".$_POST['mcNameM3']."' WHERE mcID='M3';";
$usqlM4 = "UPDATE tblmaincircles SET mcName= '".$_POST['mcNameM4']."' WHERE mcID='M4';";
$usqlM5 = "UPDATE tblmaincircles SET mcName= '".$_POST['mcNameM5']."' WHERE mcID='M5';";

Any help would be great. Thanks in advance!

Answer 1

Operator

A CASEcan do it. You can add a where clause so that the system does not evaluate each record and improve performance if the MCID is indexed.

UPDATE tblmaincircle set mcname = case when mcid = 'M1' then 'McNameM1'
                                       when mcid = 'M2' then 'McNameM2'
                                       when mcid = 'M3' then 'McNameM3'
                                       when mcid = 'M4' then 'McNameM4'
                                       when mcid = 'M5' then 'McNameM5' end
where mcid in ('M1','M2','M3','M4','M5');