Re: SQL help

Frank van Bortel wrote on 25.09.2007 20:31:
> The above is geared towards the expectation you will have
> more inserts than updates, if you anticipate to do more
> updates than inserts, reverse the insert and update:
> - - for all records to be processed
> - - loop
> - -- update the record
> - ---- if the update fails (catch the ORA-01403 exception),
> - ---- then insert;
> - -- end loop;
>
> The rationale is that trying to insert (or update),
> catching the error, processing the exception, and
> subsequently update (or insert) takes more time
> than doing a successful insert (or update).
>

If the row does not exists, the UPDATE will not "fail", it will simply return 0 (zero) rows updated. Not sure how to retrieve the number of updated records in PL/SQL, in JDBC this would be

int rowsUpdate = statement.executeUpdate("UPDATE ..."); if (rowsUpdate == 0)
{

   statement.executeUpdate("INSERT ...."); }

Thomas Received on Wed Sep 26 2007 - 03:26:59 CDT