CODEWITHSUNDEEP
mysqlsql-update
vrvictor
vrvictor

Reputation: 95

MYSQL update with sentence if

in need create sentence update need something like this:

update table1
set lp=1, mpe=0, pe=0, f2_lp=0, f1_lp=0, mvlp=0 if (lp=1)
set lp=0, mpe=1, pe=0, f2_lp=0, f1_lp=0, mvlp=0 if (mpe=1)
set lp=0, mpe=0, pe=1, f2_lp=0, f1_lp=0, mvlp=0 if (pe=1)
set lp=0, mpe=0, pe=0, f2_lp=1, f1_lp=0, mvlp=0 if (f2_lp=1)
set lp=0, mpe=0, pe=0, f2_lp=0, f1_lp=1, mvlp=0 if (f1_lp=1)
set lp=0, mpe=0, pe=0, f2_lp=0, f1_lp=0, mvlp=1 if (mvpl=1)

Upvotes: 0

Views: 75

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Just rephrase this, with the condition first. I'd normally recommend that you use case, but might be able to do what you want like this:

update table1
    set lp = (lp = 1),
        mp2 = (mp2 = 1),
        mpe = (mpe = 1),
        f2_lp = (f2_lp = 1),
        f1_lp = (f1_lp = 1),
        mvlp = (mvlp= 1);

This isn't exactly what you want. For what you seem to describe:

update table1 
     set lp = (case when lp = 1 then 1 else 0 end),
         mp2 = (case when lp = 1 then 0 when mp2 = 1 then 1 else 0 end),
         mpe = (case when lp = 1 or mp2 = 1 then 0 when mpe = 1 then 1 else 0 end),
         f2_lp = (case when lp = 1 or mp2 = 1 or mpe = 1 then 0 when f2_lp = 1 then 1 else 0 end),
          . . .

Upvotes: 1

Ike Walker
Ike Walker

Reputation: 65567

You probably want to separate this into multiple updates statements and use a WHERE clause in each one to enforce your criteria.

You should be careful with how you implement this since the order of execution is important. For example, if you have a row where lp=1 and mpe=1 what do you want to do? Do you want to set lp=0 or set mpe=0?

Here's an example based on your code snippet to get you started:

update table1
set lp=1, mpe=0, pe=0, f2_lp=0, f1_lp=0, mvlp=0 
where (lp=1)

update table1
set lp=0, mpe=1, pe=0, f2_lp=0, f1_lp=0, mvlp=0 
where (mpe=1)

update table1
set lp=0, mpe=0, pe=1, f2_lp=0, f1_lp=0, mvlp=0 
where (pe=1)

update table1
set lp=0, mpe=0, pe=0, f2_lp=1, f1_lp=0, mvlp=0 
where (f2_lp=1)

update table1
set lp=0, mpe=0, pe=0, f2_lp=0, f1_lp=1, mvlp=0 
where (f1_lp=1)

update table1
set lp=0, mpe=0, pe=0, f2_lp=0, f1_lp=0, mvlp=1 
where (mvpl=1)

Upvotes: 0

Related Questions