How to using if statement in mysql

Question

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

Do you want to update the table or just select the transformed data durasi_pre_u? — Arihant
– Arihant, Commented Sep 22, 2018 at 4:16
Should pangkat_u get updated after durasi_pre_u or before updating durasi_pre_u — Arihant
– Arihant, Commented Sep 22, 2018 at 4:27
You need to replace pangkat_u = 'Member' with 'Member' and change the <> to = — Nick
– Nick, Commented Sep 22, 2018 at 4:32

Nick · Accepted Answer · 2018-09-22 04:40:25Z

1

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)

answered Sep 22, 2018 at 4:40

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Madhur Bhaiya · Accepted Answer · 2018-09-22 04:39:34Z

0

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)

answered Sep 22, 2018 at 4:39

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

Collectives™ on Stack Overflow

How to using if statement in mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related