Re: most idiomatic way to "update or insert"?

Richard Huxton wrote:

> Ron St-Pierre wrote:
>
>> Richard Huxton wrote:
>>
>>>
>>> An "update or insert" would be useful sometimes, but it's not always
>>> necessary. Indeed, if I find I don't know whether I'm adding or
>>> updating something I take a long hard look at my design - it
>>> ususally means I've not thought clearly about something.
>>
>>
>>> Can you give an actual example of where you need this?
>>
>>
>> How about stocks for a simple example? Let's say you have a simple
>> table with the stock symbol, stock exchange, high, low, open, close
>> and volume. Every day you update the data for each stock. But there
>> are always new stocks listed on an exchange, so when a new stock
>> shows up you have to do an insert instead of an update.
>
>
> If it is just a simple table then delete all of them and insert from
> scratch. If you wanted to track changes over time (perhaps more
> likely), you'd have a separate table with the company name/address etc
> and a log table. At which point you'll want to know if it's a new
> company or not...
>
Okay, this simple example really exists, but the simple table also
includes a date that the stock was last traded, so we have:
stock symbol, stock exchange, high, low, open, close, volume, date, plus
a few more fields

But the data isn't always updated at one time, as we can update all
stocks for one exhange and possibly only some of the stocks for a
particular exchange in one go. Even if the data is received for only one
exchange we could delete all stocks for that exchange and insert new
ones, which would work fine. However some stocks are not traded every
day, so we need to show the trading information for the last date that
it was traded, so we can't delete them en masse even for the one exchange.

BTW these updates do take longer than we'd like so I would appreciate
more input on how this setup could be redesigned.

Ron