CODEWITHSUNDEEP
mysql
user1810868
user1810868

Reputation: 1615

How to insert records from table to another without duplicate

I have two tables t1 and t2. t1 has duplicated values. I need to insert all records from t1 to t2, but I don't want duplicates to occur in t2. I tried the following command which seems to me correct and no syntax error when I run it but the problem, it has 0 effect. No records from t1 inserted in t2.

insert into test.t2 (name2) 
select name1 from test.t1 where NOT EXISTS (select name2 from test.t2);

Can anybody help ?

Upvotes: 3

Views: 10739

Answers (5)

Mohammed Alfatih
Mohammed Alfatih

Reputation: 11

I need to insert data from user to another user and when write this statement .....

insert into trep12.jobhead
select
*
from
wsfin04.jobhead
where
wsfin04.jobhead.job_no not in (select job_no from trep12.jobhead)
and wsfin04.jobhead.CHASS_NO not in (select CHASS_NO from trep12.jobhead)
and rdate between '01-jul-15'
and '01-oct-15'
and job_type = 1;

..... the result is 0 rows created.

Upvotes: 0

ddoxey
ddoxey

Reputation: 2063

You can create a unique index (of one or more columns) and then use the MySQL replace command.

CREATE UNIQUE INDEX unique_name2 ON t2 (name2);

REPLACE INTO t2 (name2) values ($name1);
...

Upvotes: 1

IROEGBU
IROEGBU

Reputation: 948

this should do it:
INSERT IGNORE INTO test.t2 SELECT name2 FROM test.t1
Selects from one table and inserts into another.

Upvotes: -1

tadman
tadman

Reputation: 211670

You've go two options here, one involves not duplicating the data on your insert, the second being to ignore duplicates when inserting.

To de-duplicate from the SELECT call you'd use `DISTINCT:

INSERT INTO test.t2 (name2) SELECT name1 FROM test.t1 WHERE name1 NOT IN (SELECT name2 FROM test.t2)

You can also add a UNIQUE index to avoid this problem in the first place:

ALTER TABLE t2 ADD UNIQUE INDEX index_name2 (name2)

Note that you will get an error if there is already duplicate data in t2, so you may have to clean it up beforehand.

Then you can add data with the IGNORE option:

INSERT IGNORE INTO test.t2 (name2) SELECT name1 FROM TEST.t1

The unique index approach will guarantee uniqueness.

Upvotes: 0

AnandPhadke
AnandPhadke

Reputation: 13506

insert into test.t2(name2) 
select distinct name1 from test.t1 where name1 NOT IN(select name2 from test.t2);

OR

insert into test.t2(name2) 
select distinct name1 from test.t1 t1 where NOT EXISTS(select name2 from test.t2 t2 where t1.name1=t2.name2);

Upvotes: 7

Related Questions