Oracle SQL statement to update column value based on a matching condition-CodePudding

How can I set a column value to one of 3 values based on a specific condition?

For example, I have a table called Customer with an attribute called customer_level, where this customer can have one of 3 possible levels.

Each customer will have an attribute called customer_spent, which will show how much the customer has spent.

I have a condition where if a Customer spends >= 100, they are given the S rank. If a Customer spends >= 50 but < 100, they are given the A rank. If a Customer spends >= 0 but < 50, they are given the B rank.

So, how would I make it so when one of the conditions is matched, the customer_level will be set to either S, A or B?

I am currently trying to write this in the form of an UPDATE statement for the Customer table but am unsure of how to structure the statement.

Thanks!

CodePudding user response：

In Oracle there is a CASE/WHEN expression:

SELECT
CASE WHEN spent >= 100
     THEN 'S'
     WHEN spent >= 50 AND spent < 100
     THEN 'A'
     WHEN spent > 0 AND spent < 50
     THEN 'B'
     ELSE 'NONE'
 END as rank from table;