Re: If it Exists... Update. If it DNE... insert.

Walnut wrote:
>
> If the table/data has a primary key, why not just attempt an UPDATE
> and check if the update failed. If it did fail, INSERT it.

In most databases (I don't know about MySQL), the UPDATE won't "fail" with an error code. It might report NOT FOUND (a warning), or it might simply successfully update zero rows.

> Ruud de Koter <ruud_dekoter_at_hp.com> wrote:
> > muirwa wrote:
> > > I am using JDBC with MySql and I have one flat and relatively simple
> > > table. However, I will have to perform a large number of Inserts and
> > > update. I am concerned with the load it can handle, and its
> > > efficiency. My questions is, what is the 'best' way to go about the
> > > following:
> > >
> > > 1. Given a Record, check to see if it exists in the table (SELECT).
> > > 1.1 If it exists, UPDATE record in the table.
> > > 1.2 If it doesn't exist, INSERT record into table.
> > >
> > > However, given the simple steps above... For every one insert, there
> > > are probably 100,000 updates. So, essentially there are 99,999 wasted
> > > "SELECT" queries. But, as far as I can see it, it must be done in
> > > order to determine whether to INSERT or UPDATE.

I wrote a tool to do update/insert operations loading from a text file. It's actually available on the net as SQLUPLOAD, a part of SQLCMD, available from the IIUG Software Archive at http://www.iiug.org; beware, the SQLUPLOAD code is alpha-quality, though SQLCMD is production quality. I chose to allow the user to specify any of four options:

• UPDATE then INSERT
• INSERT then UPDATE
• SELECT then UPDATE or INSERT
• Heuristic mode

The first three are obvious; for the heuristic mode, I chose to keep a record of the last N operations (N about 20 seemed appropriate). If there more inserts than updates, then the program would attempt to insert the new first, and update the existing row if the insert failed. Conversely, if there were more updates than inserts, then the new row would be used to update an existing row and inserted if the update didn't update anything.

For your example, the 'UPDATE then INSERT' option is clearly the most appropriate,
though the heuristic mode would also work. For other scenarios, the opposite mode is most appropriate. And the heuristic mode works very well as long as you don't keep too much history -- setting N into the 100's means that you could have a sequence of 50 records in a row that should be inserted (instead of updated), but the heuristic wouldn't switch to insert then update mode because there is too much history.

-- 
Yours,
Jonathan Leffler (Jonathan.Leffler_at_Informix.com) #include <disclaimer.h>
Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN
     "I don't suffer from insanity; I enjoy every minute of it!"