CODEWITHSUNDEEP
sqloracle-databasesubquerysql-insert
Mamaf
Mamaf

Reputation: 360

How to avoid duplicate rows when inserting from one table to another with Oracle SQL?

I have two tables with the following structure :

TABLE_A

id_a|name_a|identifier_a
1   |toto  |uuuu
2   |titi  |oooo
3   |tutu  |vvvv

TABLE_B

id_b|name_b|identifier_b
1   |toto  |uuuu
11  |tyty  |tttt
56  |tetu  |bbbb

I'm trying to insert data from TABLE_B to TABLE_B without duplicate lines by using the identifier column. So, the expected result should be :

id_a|name_a|identifier_a
1   |toto  |uuuu
2   |titi  |oooo
3   |tutu  |vvvv
11  |tyty  |tttt
56  |tetu  |bbbb

The following request to do this is :

INSERT INTO TABLE_A(ID_A, NAME_A, IDENTIFIER_A)
SELECT ID_B, NAME_B, IDENTIFIER_B 
FROM TABLE_B
WHERE NOT EXISTS (SELECT IDENTIFIER_A FROM TABLE_A);

But I get an error saying that a column is not valid (???) :

SQL Error : ORA-00904: "IDENTIFIER_B" : 00904. 00000 -  "%s: invalid identifier"

Have you have better ideas to do this insert ?

Upvotes: 0

Views: 885

Answers (2)

Marmite Bomber
Marmite Bomber

Reputation: 21063

You can use a MERGE statement for this purpose - to insert only rows that doesn't exists in the target table.

Check the documentation - the MERGE statement can also update the existing rows

MERGE INTO TABLE_B b
USING TABLE_A a
ON (a.id_a = b.id_b)
WHEN NOT MATCHED THEN INSERT (id_b, name_b, identifier_b)
  VALUES (id_a, name_a, identifier_a)

Upvotes: 3

GMB
GMB

Reputation: 222442

The error message is clear enough: there is no column called identifier_b in table b. You need to use the proper column name.

Let me pinpoint, however, that your query does not do what you want: you need to correlated the EXISTS subquery, otherwise no rows will ever get inserted (unless table_a is empty to start with).

I think the logic you want is:

insert into table_a(id_a, name_a, identifier_a)
select id_b, name_b, identifier_b 
from table_b b
where not exists (select 1 from table_a a where a.identifier_a = b.identifier_b);

Upvotes: 1

Related Questions