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

Hi Muirwa (?),

muirwa wrote:
>
> I have a relatively simple question, that I can't find an explicit
> answer to. So, I thought I would ask here.
>
> 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.
>
> OR
>
> Can I assume an "UPDATE", and then check what is returned (Metadata
> maybe?) (i.e. Rows Matched: 0 Changed: 0 versus Rows Matched: 1
> Changed: 1)
>
> Any suggestions or comments would be appreciated!

Yes, you can in JDBC. Statement.executeUpdate() returns an int that represents the number of rows processed. So if you write something like:

int resultCount = stmt.executeUpdate(<Update statement>); if (resultCount == 0) {

    stmt.executeUpdate(<Insert statement>) }

this would seem to do what you require. Obviously the insert will be executed only rarely.

Have fun,

Ruud de Koter.

-- 
--------------------------------------------------------------------------------------
Ruud de Koter                    HP OpenView Software Business Unit
Senior Software Engineer         IT Service Management Operation
Telephone: +31 (20) 514 15 89    Van Diemenstraat 200  
Telefax  : +31 (20) 514 15 90    PO Box 831
Telnet   : 547 - 1589            1000 AV  Amsterdam, the Netherlands
Email    : ruud_dekoter_at_hp.com

internet: http://www.openview.hp.com/itsm
          http://www.openview.hp.com/assetview
intranet: http://ovweb.bbn.hp.com/itservicemanager
--------------------------------------------------------------------------------------