SQL statement to update a percentage change

Question

Hi guys. I have searched S.O. for this answer and have came close to an answer but still not close enough. I am interested in knowing if MySQL has this capability.

I have developed in Perl and MySQL 4 and I'm on MySQL 4 now. My table looks like this...

• symbol varchar(25)
• todayDate date
• interest int(11)

My problem is this.....these symbols (about 200,000 of them) update everyday with a new number for the interest field.

An example would be this....

symbol  | todayDate  | interest
-------------------------------
A202015 | 2010-10-26 | 150
A202015 | 2010-10-25 | 100

Ideally what I would be able to do would be to update another field at the end with a percentage change from the previous record. The above would then look like this....

symbol  | todayDate  | interest | change
-----------------------------------------
A202015 | 2010-10-26 | 150      | 50
A202015 | 2010-10-25 | 100

I didn't think that this functionality was possible in MySQL. I have come to the conclusion that I just need to grab the previous record info, do the math and then update the latest record with the percentage info. I just thought I would double check and see if any MySQL geniuses had any wisdom to pass my way.

Answer 1

From the sample data I assume the records are not "updating" but rather new records are being inserted.

INSERT INTO `rates` (`symbol`,`todayDate`,`interest`,`change`) 
    SELECT symbol,CURDATE(),$interest,$interest - `interest` 
    FROM `rates` 
    WHERE `symbol`='$symbol' AND `todayDate` = CURDATE() - INTERVAL 1 DAY

($interest and $symbol are variables containing the values you are inserting, rates is the name of the table - replace with the actual values)

Answer 2

Its a little weird because of the way MySQL references sub-queries, but this will do what you need i think:

/*

create table t_test_1 (
    symbol varchar(20) not null,
    todayDate datetime not null,
    interest int not null,
    chng int null
)

*/

insert into t_test_1 (symbol, todayDate, interest, chng) values ( 'A202015', '2010-10-09', 90, null);
insert into t_test_1 (symbol, todayDate, interest, chng) values ( 'A202015', '2010-10-10', 80, null);
insert into t_test_1 (symbol, todayDate, interest, chng) values ( 'A202015', '2010-10-11', 120, null);


update t_test_1 as t1 
    set chng = t1.interest - (select interest from (
        select *
        from t_test_1 as t11 
        ) as x 
        where x.symbol = t1.symbol and x.todayDate < t1.todayDate 
        order by x.todayDate desc
        limit 1
        );


select * from t_test_1;

this results in:

A202015 2010-10-09 90   NULL
A202015 2010-10-10 80   -10
A202015 2010-10-11 120  40

oh, i should add, this is against a mysql 5.x database server. i'm not sure if it will work against 4.x as i don't have a 4.x server to test with, sorry.

-don