The following template updates the value of a column conditionally.
UPDATE <table name>
SET <column name> =
CASE WHEN <condition met> THEN <new value> ELSE <column name> END
WHERE ......;
What it does is that if the condition is met, set the column value to the new value, otherwise, use its old value.
For example, if you have a FRUIT table with the following data.
NAME PRICE ORDER LastOrderDate
APPLE 1.99 5 12/13/2014
ORANGE 0.99 99 1/1/2015
PEAR 1.99 150 1/10/2015
If you want to reduce the price by 0.5 if by a certain order date the order is less than 100, you can use the following sql.
UPDATE FRUIT
SET PRICE =
CASE WHEN (ORDER < 100) THEN (PRICE - 0.5) ELSE PRICE END
WHERE LastOrderDate < TO_DATE('01/05/2014', 'mm/dd/yyyy');
The Result would be:
NAME PRICE ORDER LastOrderDate
APPLE 1.49 5 12/13/2014
ORANGE 0.49 99 1/1/2015
PEAR 1.99 150 1/10/2015
--------------------------------------------------------------------------------------------------------------------
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.
UPDATE <table name>
SET <column name> =
CASE WHEN <condition met> THEN <new value> ELSE <column name> END
WHERE ......;
What it does is that if the condition is met, set the column value to the new value, otherwise, use its old value.
For example, if you have a FRUIT table with the following data.
NAME PRICE ORDER LastOrderDate
APPLE 1.99 5 12/13/2014
ORANGE 0.99 99 1/1/2015
PEAR 1.99 150 1/10/2015
If you want to reduce the price by 0.5 if by a certain order date the order is less than 100, you can use the following sql.
UPDATE FRUIT
SET PRICE =
CASE WHEN (ORDER < 100) THEN (PRICE - 0.5) ELSE PRICE END
WHERE LastOrderDate < TO_DATE('01/05/2014', 'mm/dd/yyyy');
The Result would be:
NAME PRICE ORDER LastOrderDate
APPLE 1.49 5 12/13/2014
ORANGE 0.49 99 1/1/2015
PEAR 1.99 150 1/10/2015
--------------------------------------------------------------------------------------------------------------------
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.
No comments:
Post a Comment