CODEWITHSUNDEEP
mysql
Nimish Jain
Nimish Jain

Reputation: 97

MySQL: Update table column using values of same column

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?

Upvotes: 1

Views: 1466

Answers (1)

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

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'

demo on dbfiddle.uk

Upvotes: 2

Related Questions