Path: text.usenetserver.com!out01a.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!57g2000hsv.googlegroups.com!not-for-mail
From:  Charles Hooper <hooperc2000@yahoo.com>
Newsgroups: comp.databases.oracle.misc
Subject: Re: best way to conditionally insert a row
Date: Wed, 08 Aug 2007 19:12:49 -0700
Organization: http://groups.google.com
Lines: 93
Message-ID: <1186625569.128817.148600@57g2000hsv.googlegroups.com>
References: <1Tsui.1271$3x.761@newssvr25.news.prodigy.net>
NNTP-Posting-Host: 205.208.133.102
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
X-Trace: posting.google.com 1186625569 29192 127.0.0.1 (9 Aug 2007 02:12:49 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Thu, 9 Aug 2007 02:12:49 +0000 (UTC)
In-Reply-To: <1Tsui.1271$3x.761@newssvr25.news.prodigy.net>
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.0.04506),gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: 57g2000hsv.googlegroups.com; posting-host=205.208.133.102;
   posting-account=ps2QrAMAAAA6_jCuRt2JEIpn5Otqf_w0
Xref: usenetserver.com comp.databases.oracle.misc:249172
X-Received-Date: Wed, 08 Aug 2007 22:12:49 EDT (text.usenetserver.com)

On Aug 8, 8:05 pm, Mark Harrison <m...@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.