MySQL: Update table column using values of same column

Question

I have a MySQL table:

PriceRange (MinPrice DOUBLE, MaxPrice DOUBLE, TrdDate DATE)

I want to apply a multiplication factor on MinPrice and MaxPrice for all the records with TrdDate less than a particular date. For example,

Table before update:

(10, 12, 01-JAN-2016)
(12, 14, 02-JAN-2016)
(6, 7, 03-JAN-2016)

Table after update (with multiplication factor of 0.5 applied on all records for TrdDate < 03-JAN-2016):

(5, 6, 01-JAN-2016)
(6, 7, 02-JAN-2016)
(6, 7, 03-JAN-2016)

I know subquery will not work here as I can't update the same record that I am reading.

Can I solve above problem using JOIN or do I have to use cursor for it?

@Sumon Sarker: A date has no format; it's just a date (i.e. 2017-07-15 is the same date as July 15, 2017 of course). — Thorsten Kettner
– Thorsten Kettner, Commented Apr 3, 2017 at 7:25
Yes! I know, But he mentioned the date format like that. Because date string comparison is not valid when 01-JAN-2016 and 01-APR-2016 @ThorstenKettner — Sumon Sarker
– Sumon Sarker, Commented Apr 3, 2017 at 7:27
@Sumon Sarker: It doesn't matter how the date is shown in some output or sample. In the database it is still a mere date. You can compare it to any valid supported date string literal, e.g. '2016-01-03'. It is recommended to use ANSI date literals though: DATE '2016-01-03'. — Thorsten Kettner
– Thorsten Kettner, Commented Apr 3, 2017 at 7:33

Sebastian Brosch · Accepted Answer · 2019-11-16 15:16:51Z

2

You can use a simple UPDATE query to solve this:

UPDATE PriceRange 
SET MinPrice = MinPrice * 0.5, MaxPrice = MaxPrice * 0.5 
WHERE TrdDate < '2016-01-03'

answered Apr 3, 2017 at 7:17

Sebastian Brosch

43.8k15 gold badges78 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1