CODEWITHSUNDEEP
mysqlsql
angelique000
angelique000

Reputation: 909

IF statement when update a column

I have a table Products with this content:

  | Product title | price | price_sales | new_price |
1 | Product A     | 100   | 80          | ???       |
2 | Product B     | 100   | 0           | ???       |
3 | Product C     | 400   | 200         | ???       |

I have to do an update query about all products: the column new_price has to be a column with 10% discount on the current price. When there is no price_sales (price_sales = 0), the column price is the actual price.

The result has to be:

  | Product title | price | price_sales | new_price |
1 | Product A     | 100   | 80          | 76        |
2 | Product B     | 100   | 0           | 90        |
3 | Product C     | 400   | 200         | 180       |

Upvotes: 0

Views: 35

Answers (3)

somsgod
somsgod

Reputation: 357

Try this.

UPDATE Products SET new_price = if(price_sales > 0, (price * 10.0 / 100.0), price)

Upvotes: 0

potashin
potashin

Reputation: 44581

You can try the following:

update tbl set new_price = if(price_sales > 0, price_sales, price) * 0.9

Upvotes: 1

fancyPants
fancyPants

Reputation: 51868

Here you go:

update tbl 
set new_price = if(price_sales > 0, price_sales * 0.9, price * 0.9)

Upvotes: 2

Related Questions