I have a database containing stock data which I want to keep up to date.
The relevant fields are the symbol, date, high, low, and last prices.
When a new price comes in for an existing date, I want to update the high if the price is
higher, the low if the price is lower, and I always want to update the last price.
If a price comes in with a new date, I want to insert a new record and put the price into
the high, low, and last price fields.
The way I'm doing it now works, but I'm not sure it's best.
Basically, I look to see whether the currect date exists by retrieving the high and low
for the date. If it succeeds, I calculate whether the new price is higher than the
existing high or lower than the existing low. Then I update the high, low and last price.
If the date retreival fails, I do an insert.
My questions are:
1) is there a way to do either an insert or an update and not have to check if a bar
exists first? Should I use replace and it is just as quick?
2) Is there a way to update the highs and lows in one SQL statement without having to
fetch any info from the database first?