Oracle 10g - INSERT statement only if record doesn't currently exist

Databases
1

Hello, everyone.

I’m trying to write a query that will insert new data into a table based upon a query from a different db server passed via http. It needs to first make sure that the data on the new db server doesn’t already exist.

I’ve tried:


INSERT into tableName(colA,colB,colC,etc)
VALUES(vlu1,vlu2,vlu3,etc)
WHERE NOT EXISTS(SELECT colA FROM tableName WHERE colB = //HTTP value B// and colC = //HTTP value C//)

With the above, I’m getting “SQL command not properly ended”. I’ve tried with and without semi-colon (;).

Any relevant and sincere suggestions appreciated.

V/r,

:slight_smile:

2

you cannot add a WHERE clause to the INSERT VALUES syntax

looks like you’ll have to write a SELECT first, and then do a logic test on the result to decide whether to run the INSERT

3

This isn’t working, either:


DECLARE cnt NUMBER;
BEGIN
  SELECT COUNT(USER_NAME) INTO cnt FROM schema.tableA WHERE USER_IP = '127.0.0.1'
  IF(cnt = 0)
    THEN
      SELECT USER_NAME FROM schema.tableA WHERE USER_IP = '123.213.123.213'
    ELSE
      SELECT USER_NAME FROM schema.tableA WHERE USER_IP = '127.0.0.1'
    END IF;
END;

ERROR starting at line 1 in command
ORA-06550: line 6 ‘an INTO clause is expected in this SELECT statement’
ORA-06550: line 8 ‘an INTO clause is expected in this SELECT statement’

V/r,

:slight_smile:

4

sorry i cannot help you further, i’m not familiar with oracle variables and IF/ELSE