CODEWITHSUNDEEP
phpmysqlpdo
Jazuly
Jazuly

Reputation: 1414

How to using if statement in mysql

i want to minus 1 (-1) value from durasi_pre_u if durasi_pre_u have basic value bigger then 0 and update Premium to Member when durasi_pre_u have value = 0.

here what i try

UPDATE user 
SET durasi_pre_u = IF(durasi_pre_u > 0, durasi_pre_u - 1, durasi_pre_u), 
pangkat_u = IF(durasi_pre_u <> 1 AND pangkat_u = 'Premium', pangkat_u = 'Member', pangkat_u)

this code working -1 durasi_pre_u but not working change Premium to Member

i try to follow tutorial from this site https://www.w3resource.com/mysql/control-flow-functions/if-function.php

Upvotes: 0

Views: 21

Answers (2)

Nick
Nick

Reputation: 147166

You have an error in your query, firstly you only want to change the value of pangkat_u when the current value of durasi_pre_u is 1 (so it is about to change to 0) so you need to change the <> to =. Secondly you have pangkat_u = 'Member' as the new value, which is treated as a boolean expression (i.e. a value of 1 or 0) by MySQL. What you actually want is just 'Member'. So your query should be:

UPDATE user 
SET durasi_pre_u = IF(durasi_pre_u > 0, durasi_pre_u - 1, durasi_pre_u), 
pangkat_u = IF(durasi_pre_u = 1 AND pangkat_u = 'Premium', 'Member', pangkat_u)

Upvotes: 1

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

In If function, you only need to specify the values to be set in case of true or false. Dont specify the field name = value (pangkat_u = 'Member')

Use the following : 

UPDATE user 
SET durasi_pre_u = IF(durasi_pre_u > 0, durasi_pre_u - 1, durasi_pre_u), 
pangkat_u = IF(durasi_pre_u <> 1 AND pangkat_u = 'Premium', 'Member', pangkat_u)

Upvotes: 0

Related Questions