SQL to update percentage change

Question

I searched SO for this answer and came up with the answer, but still not close enough. I am interested to know if MySQL has this feature.

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

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

My problem is ..... these characters (about 200,000 of them) are updated every day with a new number for the field of interest.

An example is this ....

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

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

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

, MySQL. , , , . , , - MySQL - , .

Answer 1

- , :

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 interest from (
                    select *
                    from t_test_1 as t11 
                ) as x2
                where x2.symbol = t1.symbol and x2.todayDate < t1.todayDate 
                order by x2.todayDate desc
                limit 1
            ) * 100 ;

Answer 2

- , MySQL , , , :

/*

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;

:

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

oh, , mysql 5.x. , 4.x, 4.x , .

-don

Answer 3

From the example data, I assume that the records are not "updated", but new records are 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 that contain the values ​​you insert rates- this is the name of the table - replace with actual values)