Home > Software engineering >  inserts values into a table if the value does not exists
inserts values into a table if the value does not exists

Time:10-28

I am trying to insert values into a table where if the values does not exist then it should insert them but I am having issue with this SQL.

 insert into table1 (id, name, value)
    select Id, Name, Value
    from table2
    where NOT EXISTS typeId = TypeId;

CodePudding user response:

Should've been something like this:

INSERT INTO table1 (id, name, VALUE)
   SELECT id, name, VALUE
     FROM table2 b
    WHERE NOT EXISTS
             (SELECT NULL
                FROM table1 a
               WHERE a.typeid = b.typeid);

CodePudding user response:

This is what a MERGE statement is good at:

MERGE INTO table1 dst
USING table2 src
ON (src.typeID = dst.typeID)
WHEN NOT MATCHED THEN
  INSERT (id, name, value) VALUES (src.Id, src.Name, src.Value);

Which, for the sample data:

CREATE TABLE table1 (id PRIMARY KEY, typeid, name, value) AS
SELECT 2, 2, 't1-name2', 2 FROM DUAL UNION ALL
SELECT 4, 4, 't1-name4', 4 FROM DUAL;

CREATE TABLE table2 (id PRIMARY KEY, typeid, name, value) AS
SELECT 1, 1, 't2-name1', 1 FROM DUAL UNION ALL
SELECT 2, 2, 't2-name2', 2 FROM DUAL UNION ALL
SELECT 3, 3, 't2-name3', 3 FROM DUAL UNION ALL
SELECT 4, 4, 't2-name4', 4 FROM DUAL UNION ALL
SELECT 5, 5, 't2-name5', 5 FROM DUAL;

Then, after the MERGE:

SELECT * FROM table1;

Outputs:

ID TYPEID NAME VALUE
2 2 t1-name2 2
4 4 t1-name4 4
3 t2-name3 3
5 t2-name5 5
1 t2-name1 1

(Note: you probably want to include TYPEID in the columns you insert.)

db<>fiddle here

  • Related