Search code examples
mysqlheidisql

Update Statement with Case in MYSQL

I have this issue and I have investigated more than five house but find nothing :( . I have table called support.

UPDATE support s SET    s.Survey_Status = 0
CASE 
WHEN s.Survey_Status = 0 Then 1
WHEN s.Survey_Status = 1 Then 2 
End 
Where last_response < ADDDATE(CURDATE(), INTERVAL 1 DAY) 
and support_waiting ="n" ;

I need to update the support table and set the survey_status =1 except the fifth row in the table will be =2 . For example, if I have survey_ status from 1 to 10 = 1 then the fifth will =2 . any idea please ?? By the way, I am working with mysql Heidi .

Thanks in Advance

Solution

  • You can combine user variables and MOD():

    UPDATE   support, (SELECT @r:=0) init
SET      Survey_Status = IF(MOD(@r:=@r+1,5), 1, 2)
WHERE    last_response < CURRENT_DATE + INTERVAL 1 DAY
     AND support_waiting = 'n'
ORDER BY ...