Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: best way to conditionally insert a row
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