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

Reply via email to