CODEWITHSUNDEEP
databaseoracle-databaseoracle11ginsertduplicates
Kevin Romero
Kevin Romero

Reputation: 17

Oracle 11g Insert into from another table that has duplicates

Ok so I have 2 new tables: Client and Contract. I'm gonna focus on the first one as they have the same structure. Client looks like:

+-----------+---------+
| client_id |  name   |
+-----------+---------+
| Value 1   | Value 2 |
+-----------+---------+

And created like this:

CREATE TABLE Client (
  client_id varchar2(15)  NOT NULL,
  name varchar2(100)  NOT NULL,
  CONSTRAINT Client_pk PRIMARY KEY (client_id)
) ;

Also I have an old table: old_contracts looking like:

+------------+----------+------+
| contractid | clientid | name |
+------------+----------+------+
| con1       | cli1     | n1   |
| con2       | cli2     | n2   |
| con3       | cli2     | n2   |
| con4       | cli3     | n3   |
| con5       | cli3     | n3   |
+------------+----------+------+

Defined:

CREATE TABLE old_contracts(
  contractid varchar2(15)  NOT NULL
  clientid varchar2(15)  NOT NULL,
  name varchar2(100)  NOT NULL
) ;

I want to take the data from old_contract and insert it into Client.

This old_contracts table has rows with duplicate clientid (one client can have more than one contract) but I don't want to have duplicates on Client table so I am doing this:

INSERT INTO Client (
  client_id,
  name
) SELECT DISTINCT
  clientid,
  name
FROM old_contracts;

to not get duplicates. Anyway, I'm getting this error:

Error SQL: ORA-00001: unique constraint (USER.CLIENT_PK) violated

00001.00000 - "unique constraint (%s.%s) violated"

What's going on? I believe the DISTINCT keyword was going to do the thing.

I've also tried adding a WHERE NOT EXISTS clause as suggested in related posts (i.e. this one), but the result I'm getting is the same error.

Upvotes: 0

Views: 131

Answers (1)

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36977

Most likely, the name is not always the same for a given clientid.

Try this instead:

INSERT INTO Client (
 client_id,
  name
) SELECT clientid,
  max(name)
FROM old_contracts
GROUP BY clientid;

Upvotes: 1

Related Questions