I have a table created as such:
CREATE TABLE `full_daily_data` (
`Symbol` varchar(9) NOT NULL,
`Trade_Date` date NOT NULL DEFAULT '0000-00-00',
`Day_Open` float DEFAULT NULL,
`Day_High` float DEFAULT NULL,
`Day_Low` float DEFAULT NULL,
`Day_Close` float DEFAULT NULL,
`Prior_Open` float DEFAULT NULL,
`Prior_High` float DEFAULT NULL,
`Prior_Low` float DEFAULT NULL,
`Prior_Close` float DEFAULT NULL,
PRIMARY KEY (`Symbol`,`Trade_Date`),
KEY `TradeDate` (`Trade_Date`) );
I have it populated with data in these fields:
What I want to do is have a SQL statement which will do one of two things:
1. With the data in symbol, then date order, take the data for the open, high, low and close from the row for one day and put them in the corresponding 'prior_' fields for the row in the corresponding following day (assuming that the symbol is the same). Sounds like an aggresive if not impossible thing to do in just SQL... is it?
or, if not possible, then:
2. have a SQL statement that simply gets the data from one day for a symbol, and puts the open, high, low, close into the corresponding "Prior_" values in the row for the next day.
Any suggestions on this?