Update query

Occasional Contributor

Hello community!

I need to update the field price of a table with this condition: 

to all type C items, increase the value of the price of type A items by 65%.
That is to say:
Item - Price - Type
Item1 - 10 - A
Item2 - 15 - A
Item1 - 12 - B
Item2 - 20 - B
Item1 - (10 + (10+ (10+65%)) - C
Item2 - (15 + (15+(15+65%)) - C

 

What is the correct SQL statement to be able to perform this table record update?

Thanks and Happy New Year!

4 Replies

to all type C items, !


@RaulSG ; what for C items, I don't see one?

 

Please post table design as DDL, some sample data as DML statement and the expected result.

@olafhelper Sorry,

From:

Item Price Type
Item1 - 10 - A
Item2 - 15 - A
Item1 - 12 - B
Item2 - 20 - B
Item1 - 1 - C
Item2 - 1 - C

 

To:

Item Price Type
Item1 - 10 - A
Item2 - 15 - A
Item1 - 12 - B
Item2 - 20 - B
Item1 - (10+ (10*65%)) - C
Item2 - (15+(15*65%)) - C

 

And which role does Type = "B" play; none?

Again, p0lease post table design as DDL, some sample data as DML statement and the expected result.

@RaulSG , something like this?

 

SELECT A.Price * 1.65 - C.Price
FROM yourTable AS A
     INNER JOIN
     yourTable AS C
         ON A.Item = C.Item
WHERE A.Type = 'A'
      AND C.Type = 'C'