CODEWITHSUNDEEP
mysql
developer
developer

Reputation: 2050

Inserting distinct entries into the database

I have two tables with exactly the same fields. Table A contains 7160 records and table B 7130 records.Now I want to insert distinct records from table A into table B such that B should not have any duplicate entry in it. How should I go about doing this?

Upvotes: 0

Views: 996

Answers (3)

Rashmi Pandit
Rashmi Pandit

Reputation: 23798

insert into tableB (id)
select t1.id from tableA t1
where t1.id not in (select t2.id from tableB t2)

Upvotes: 0

Vladiat0r
Vladiat0r

Reputation: 595

This basically selects records that are in A that are not in B. It would work, but you might have to tweak the field you use to uniquely identify a record. In this example I used field 'ID' but you might have to change that to A.field1 = B.field1 AND A.field2 = B.field2 etc.

INSERT INTO TABLEB
(
SELECT A.*
FROM TABLEA A
LEFT JOIN TABLEB B ON A.ID = B.ID
WHERE B.ID IS NULL
)

Upvotes: 1

Andy White
Andy White

Reputation: 88345

You can use a "union" query to combine the results from multiple tables into a single result set. "union" will only return distinct rows from all tables.

See this page for more info:

http://www.tutorialspoint.com/mysql/mysql-union-keyword.htm

Upvotes: 1

Related Questions