On 3 Jun 2002, at 10:06, Mike G wrote:
> 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?
REPLACE deletes the existing data before inserting the record, so it
wouldn't work for your purpose.
> 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?
You could do something like
UPDATE stock_prices
SET high = GREATEST(high, $price),
low = LEAST(low, $price),
last = $price
WHERE date = '$date'
AND symbol = '$symbol';
but you'd still have to check to see whether the update happened and
if not then do an insert. It's tricky because if the record already
exists and its last price happens to be equal to the current price,
then no update will occur (because there's no change and MySQL
optimizes the query away), so mysql_affected_rows() won't work. One
solution would be to add a last_update_time column that would always
change, but it may not be worth it if you'd have no other use for it.
--
Keith C. Ivey <[EMAIL PROTECTED]>
Tobacco Documents Online
http://tobaccodocuments.org
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
