Open Source RDBMS - Seamless, Scalable, Stable and Free

English | Login |Register


1
(click on this box to dismiss)

how to remove duplicates from a table

How do i remove duplicate rows from the table below before changing id to be a primary key?

CREATE TABLE t(id INTEGER, code char(20));

링크 댓글 쓰기 (0)
질문시간 3년 전
eusto
204
11 답변들
0

Since there can be duplicate records where the values of the code column are different, this solution assumes that you do not care which of these duplicate records will be kept.

DROP TABLE IF EXISTS t2;
CREATE TABLE t2 LIKE t;

INSERT INTO t2 (id, code)
		SELECT id, code FROM t
		GROUP by id
		HAVING COUNT(id) > 1;

DELETE FROM t
WHERE id IN (
		SELECT id FROM t2
);

INSERT INTO t (id, code)
		SELECT id, code FROM t2;

DROP TABLE t2;

  1. First, you need to create a temporary table t2 similar to table t which will hold the duplicate records.
  2. Then insert to t2 the representative duplicate records. The table will hold records with unique id. For example, if there are 3 records where id = 3, only one of them will inserted thus guaranteeing the uniqueness.
  3. Delete all duplicate records from the original table.
  4. Insert back from t2 to t those unique records.
  5. Delete the temporary table.


링크 댓글 쓰기 (0)
답변시간 3년 전
dba
558
tagged




You are either using a very old browser or a browser that is not supported.
In order to browse cubrid.org you need to have one of the following browsers:



Internet Explorer: Mozilla Firefox: Google Chrome: