Re: SQL help

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Laura wrote:
> Hi,
>
> I hope this is the correct group to post in - any help is appreciated.
>
> I am writing some SQL to populate or update an Oracle table. I
> currently run this through JDBC, however, it will probably end up as a
> PL/SQL procedure.
>
> What I want to do is UPDATE a row if it currently exists in the
> database - however, if it doesn't already exist, I want to CREATE it
> with the new values.
>
> I'm sure this must be a fairly common thing to do - however, my
> searches for an answer have been unsuccessful... I would prefer not
> to have to run a separate query in my code to determine whether an
> update or insert is required.
>
> Thanks,
> - Laura
>

Steve already explained about merge - it might be difficult to achieve if:
- - you're on a version, that does not support merge - - your data comes from another source, e.g. your jdbc connection   (although 10G allows merges between tables and sets).

In that case, lookup exception handling. the basic idea is:

- - for all records to be processed
- - loop
- -- insert the record
- ---- if the insert fails (catch the ORA-00001 exception),
- ---- then update;
- -- end loop;

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).

Prerequisite is that the table you want to update/insert has a primary key constraint (if not, the exception cannot be raised)
The ORA-00001 error is defined as DUP_VAL_ON_INDEX the ORA-01403 as NO_DATA_FOUND.
- --
Regards,
Frank van Bortel

Top-posting is one way to shut me up...
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32)

iD8DBQFG+VPmLw8L4IAs830RAijFAJ9RnBA/hN7GFQ9aFXu1ChgSzAIyygCfTPlu s6bPqHRNxp1LP3QKOt0s6+k=
=UQhM
-----END PGP SIGNATURE----- Received on Tue Sep 25 2007 - 13:31:02 CDT