Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: best way to conditionally insert a row

Re: best way to conditionally insert a row

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Wed, 08 Aug 2007 19:12:49 -0700
Message-ID: <1186625569.128817.148600@57g2000hsv.googlegroups.com>


On Aug 8, 8:05 pm, Mark Harrison <m..._at_pixar.com> wrote:
> I would like to insert a row into a table, but only
> if the primary key does not exist.
>
> For example, if I have a table "keywords" with
> columns "name" and "val", I would like
>
> insert into keywords (name, val) values ('color', 'blue');
>
> to happen only if there is not already a row where
> name='color'.
>
> What is the best way to do this?
>
> Many TIA!
> Mark

One method:
Set up a basic table:
CREATE TABLE T1 (
  COL_NAME VARCHAR2(10),
  COL_VALUE VARCHAR2(10),
  PRIMARY KEY (COL_NAME)); Table created.

Now, try an insert statement, selecting the values to be inserted from DUAL, and then performing an outer join to the table - specifying that a row should not be found in the table:
INSERT INTO
  T1
SELECT /*+ ORDERED */
  D.COL_NAME,
  D.COL_VALUE
FROM
  (SELECT
    'COLOR' COL_NAME,
    'BLUE' COL_VALUE
  FROM
    DUAL) D,
  T1
WHERE
  D.COL_NAME=T1.COL_NAME(+)
  AND T1.COL_NAME IS NULL; 1 row created.

Let's try again with the same input:
INSERT INTO
  T1
SELECT /*+ ORDERED */
  D.COL_NAME,
  D.COL_VALUE
FROM
  (SELECT
    'COLOR' COL_NAME,
    'BLUE' COL_VALUE
  FROM
    DUAL) D,
  T1
WHERE
  D.COL_NAME=T1.COL_NAME(+)
  AND T1.COL_NAME IS NULL; 0 rows created.

Let's try again with a different primary key value: INSERT INTO
  T1
SELECT /*+ ORDERED */
  D.COL_NAME,
  D.COL_VALUE
FROM
  (SELECT
    'SKY' COL_NAME,
    'BLUE' COL_VALUE
  FROM
    DUAL) D,
  T1
WHERE
  D.COL_NAME=T1.COL_NAME(+)
  AND T1.COL_NAME IS NULL; 1 row created.

This topic was discussed a couple months ago, and a couple other solutions were offered. Check the Google archives of this group and comp.databases.oracle.server for the other solutions.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Wed Aug 08 2007 - 21:12:49 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US