Oracle 11g Insert into from another table that has duplicates

Question

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.

Erich Kitzmueller · Accepted Answer · 2017-03-07 11:32:13Z

1

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;

answered Mar 7, 2017 at 11:32

Erich Kitzmueller

37k5 gold badges85 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Kevin Romero Over a year ago

This let me insert the data, but now I've got another problem: When I try to do the same thing with the contract table (which has field Client as foreign key (contract_client_fk) to referenced to client.client_id) I get "ORA-02291: integrity constraint (user.contract_client_fk) violated - parent key not found"

Kevin Romero Over a year ago

Must say that client column in Contract table is nullable.

Erich Kitzmueller Over a year ago

Nullable is not an issue here... but since you have populated Client with all client_ids that exist in old_contracts, I see no way how you could violate that constraint when you populate Contract with old_contracts. Maybe a little mistake that made you try to insert the contract_id into the client_id column or something like that?

Kevin Romero Over a year ago

Ok forget what I said. This issue whas related to a typo. Thanks for all.

user5683823 Over a year ago

@KevinRomero - is this the right solution for the business problem though? I agree with ammoQ, it is likely that the original problem was caused by having more than one name associated with the same clientid, for at least one clientid. In this case, the solution offered here essentially says "pick any name that was associated with the clientid in the old table and use it for the new table; ignore all the other names for the same clientid". There are other choices, for example use the longest name, or the shortest one, or the one associated with the most recent contract, etc.

|

Collectives™ on Stack Overflow

Oracle 11g Insert into from another table that has duplicates

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related