CODEWITHSUNDEEP
postgresqlcase
rac3b3nn0n
rac3b3nn0n

Reputation: 881

SQL How to update two fields using CASE WHEN THEN

I hope the title explains it all but I will still try and explain what I am trying to achieve. So I have a table called users, I need to check if the users have the following statements true,

If users have above values true then change their enabled status to false. At the same time, I need to update disabledTime field in table with current time.

I've written the following SQL which is able to set enabled = false for the users but not sure how I can set disabledTime field. 

UPDATE users
SET
   enabled = CASE
   WHEN
       enabled = true
       AND lastaccess != 0
       AND lastaccess < (EXTRACT('epoch' FROM CURRENT_TIMESTAMP) - (86400*200))*1000
   THEN false
   ELSE enabled
END

The above SQL sets user's enabled status as false but disabledTime field remains empty.

Any help on this will be greatly appreciated.

Upvotes: 0

Views: 59

Answers (1)

Oto Shavadze
Oto Shavadze

Reputation: 42773

Just copy same condition for disabledTime column

UPDATE users SET
enabled = CASE
WHEN
    enabled = true
    AND lastaccess != 0
    AND lastaccess < (EXTRACT('epoch' FROM CURRENT_TIMESTAMP) - (86400*200))*1000
THEN false
ELSE enabled
END,
disabledTime = 
CASE
WHEN
    enabled = true
    AND lastaccess != 0
    AND lastaccess < (EXTRACT('epoch' FROM CURRENT_TIMESTAMP) - (86400*200))*1000
THEN CURRENT_TIMESTAMP
ELSE disabledTime
END

Upvotes: 1

Related Questions