Update field with other fields data minus one day

[chris]

Hi

I really don't know how to go about this in a relational database.

There is data thats downloaded from the net everyday. The data includes a
opening price of stock. What I need in the database is to update the today's
opening price with yesterday's closing price.

eg
Instrument, Date, Opening price, Closing Price
ABI 20021028 <Null> 4499
ABI 20021029 <Null> 4410

The opening price for 20021029 should be 4499

Any ideas
Many thanks
Chris

[Jacco Schalkwijk]

Assuming that there is a Primary Key on (Instrument, date)

UPDATE myTable
SET OpeningPrice = (SELECT ClosingPrice FROM MyTable t2
WHERE t2.date = myTable.date -1 AND t2.Instrument = myTable.Instrument)

Or if you don't have prices for everyday, get the price for the last day for
which there is one:
UPDATE myTable
SET OpeningPrice = (SELECT TOP 1 ClosingPrice FROM MyTable t2
WHERE t2.date < myTable.date AND t2.Instrument = myTable.Instrument
ORDER BY t2.date DESC)

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.


"chris" <chris@nutcracker.co.za> wrote in message
news:O57rE#tRDHA.3700@tk2msftngp13.phx.gbl...

> Hi
>
> I really don't know how to go about this in a relational database.
>
> There is data thats downloaded from the net everyday. The data includes a
> opening price of stock. What I need in the database is to update the

today's

> opening price with yesterday's closing price.
>
> eg
> Instrument, Date, Opening price, Closing Price
> ABI 20021028 <Null> 4499
> ABI 20021029 <Null> 4410
>
> The opening price for 20021029 should be 4499
>
> Any ideas
> Many thanks
> Chris
>
>

[Stefan Gustafsson]

UPDATE StockTable
SET OpeningPrice = (
SELECT TOP 1 ClosingPrice
FROM StockTable S
WHERE S.Instrument = StockTable.Instrument
AND S.Date < StockTable.Date
ORDER BY S.Date DESC)


"chris" <chris@nutcracker.co.za> wrote in message
news:O57rE#tRDHA.3700@tk2msftngp13.phx.gbl...

> Hi
>
> I really don't know how to go about this in a relational database.
>
> There is data thats downloaded from the net everyday. The data includes a
> opening price of stock. What I need in the database is to update the

today's

> opening price with yesterday's closing price.
>
> eg
> Instrument, Date, Opening price, Closing Price
> ABI 20021028 <Null> 4499
> ABI 20021029 <Null> 4410
>
> The opening price for 20021029 should be 4499
>
> Any ideas
> Many thanks
> Chris
>
>