Issue with update query in a stored procedure

[juddymarch]

Hi, I'm new to MYSQL to please bear with me.
I have a table called tblEquityDetailDaily:
fldRecordID fldEquityDateID fldDate fldLiveShares
1 55 1/1/08 100
2 55 2/1/08 200
3 60 1/1/08 500
4 60 2/1/08 0
5 60 3/1/08 500
6 70 1/1/08 0
7 70 2/1/08 0

What I'm trying to do is if the liveshares are equal to 0 I want to update it to the previous value where fldEquityDateID is the same so record 4 would be updated to 500 as the previous value was on the 1/1/08. I have written a stored procedure which works but will fall over if the first value is 0 or all of the values are 0 for a particular equitydateid.
-------------------------------------------------------
CREATE Temporary TABLE TempA
SELECT * FROM tblEquityDetailDaily WHERE fldLiveShares <> 0;

UPDATE tblequitydetaildaily SET tblequitydetaildaily.fldLiveShares = (

SELECT fldLiveShares FROM TempA
WHERE TempA.fldDate < tblequitydetaildaily.fldDate AND
TempA.fldEquityDateID = tblEquityDetailDaily.fldEquityDateID AND TempA.fldLiveshares <> 0
ORDER BY TempA.fldDate DESC
LIMIT 1)
WHERE tblequitydetaildaily.fldLiveShares=0;

DROP temporary TABLE TempA;

--------------------------------------------------------
I am using a temp table as i couldn't get it to work using the same table or an alias. Just wondering how can I prevent it from falling over if the first record is 0 or all records are 0 for the same equitydateid. I have done a few searches and can't seem to find a solution with out going into cursors etc.

Any help would be greatly appreciated.
Thanks
Justin

 

[hvass]

This is how I would do it hoping I understand what you are after... I know it is a bit long winded but then I am sure it will spur rudy/tony or one of the others to come back with a much simpler version.

First off I have created the dataset and added a few more cases

drop table if exists test.equityid_original;
create table test.equityid_original
	(
		recordid int(8) unsigned not null auto_increment primary key,
		equitydateid int(8) unsigned not null,
		edate date,
		liveshares int(8) unsigned not null
	);
insert into test.equityid_original (equitydateid,edate,liveshares) values
(55,'20080101',100),
(55,'20080101',200),
(60,'20080101',500),
(60,'20080102',0),
(60,'20080103',700),
(70,'20080101',0),
(70,'20080101',0),
(80,'20080101',600),
(80,'20080102',200),
(80,'20080103',0),
(90,'20080101',0),
(90,'20080102',0)
;

drop table if exists test.equityid;
create table test.equityid
select	
	*
from test.equityid_original;
select * from test.equityid;

Step 1 : joining the table to itself to get previous dates value of liveshares

select
	*
from test.equityid a left join test.equityid b using(equitydateid) where b.edate<a.edate and b.liveshares>0;

Next problem is that you need the value of liveshare for the previous time period so find row max
(before anyone comments I know this is a classic sub query but being so old and set in my pre mysql v4.1 days
where we had no sub queries got to kind of like my old ways - concatenating fields to get the row max)

select
	equitydateid,
	concat(edate,":",liveshares) as createsinglekey,
	max(concat(edate,":",liveshares)) as findmaximum,
	substring_index(max(concat(edate,":",liveshares)),":",-1) as previousval
from test.equityid
group by equitydateid;

Putting this self join and row max together to generate a temporary table that has the values I want to update

drop table if exists test.temppreviousval;
create test.temppreviousval
select
	equitydateid,
	substring_index(max(concat(b.edate,":",b.liveshares)),":",-1) as previousval
from test.equityid a left join test.equityid b using(equitydateid) where b.edate<a.edate and b.liveshares>0
group by equitydateid;

Now the simple update statement

update test.equityid a inner join test.temppreviousval b using(equitydateid)
set a.liveshares=b.previousval
where a.liveshares=0 and previousval>0;

Put all the above together as one statement

update test.equityid a inner join 
(select
	equitydateid,
	substring_index(max(concat(b.edate,":",b.liveshares)),":",-1) as previousval
from test.equityid a left join test.equityid b using(equitydateid) where b.edate<a.edate and b.liveshares>0
group by equitydateid
) b using(equitydateid)
set a.liveshares=b.previousval
where a.liveshares=0 and previousval>0;

select * from test.equityid_original;
select * from test.equityid;

 

[juddymarch]

Thanks for your response hvass. I will have a look at your suggestion monday and let you know how I go.
Still trying to get my head around writing queries like this after years of development using the access query designer, slowly getting there

Thanks
Justin