Now there is A B C three tables,
A table field A b c primary key from increased;
B a d e primary key table field according to generate a table;
Primary key table field f a C g f since, according to a generated a table;
Each table has duplicate data, how to delete the duplicate data?

CodePudding user response:

Test data is given, and to weight rules,

CodePudding user response:

The

refer to the original poster this_mx response:

now have A B C three tables,
A table field A b c primary key from increased;
B a d e primary key table field according to generate a table;
Primary key table field f a C g f since, according to a generated a table;
Each table has duplicate data, how to delete the duplicate data?

You say so, others can't answer,
Need to provide original data and achieve the effect of, and to the heavy rule,

CodePudding user response:

The primary key is created, repeated data into complains of ah,
Give your data and the results you want

CodePudding user response:

The DELETE C WHERE ROWID IN (SELECT MAX (ROWID) FROM GROUP C BY F, A, G);
The DELETE B WHERE ROWID IN (SELECT MAX (ROWID) FROM GROUP B BY A, D, E);
DELETE A WHERE ROWID IN (SELECT MAX (ROWID) FROM A GROUP BY A, B, C);
COMMIT;

CodePudding user response:

reference 4 floor AHUA1001 response:

DELETE C WHERE ROWID IN (SELECT MAX (ROWID) FROM GROUP C BY F, A, G);
The DELETE B WHERE ROWID IN (SELECT MAX (ROWID) FROM GROUP B BY A, D, E);
DELETE A WHERE ROWID IN (SELECT MAX (ROWID) FROM A GROUP BY A, B, C);
COMMIT;

You this 3 delete statement, it is deleted article 0 data,

CodePudding user response:

Personal opinion: go to heavy as far as possible, from the perspective of the direction of the rowid
For example: Oracle
Building data
DROP TABLE t purge;
The CREATE TABLE t AS SELECT * FROM dba_objects WHERE rownum<=10;
The UPDATE t SET object_id=rownum;
The UPDATE t SET object_id=3 WHERE object_id & lt;=3;
The UPDATE t SET object_id=4 WHERE object_id & gt;=4 AND object_id & lt;=6;
COMMIT;
The first according to the Max (rowid) or min (the rowid)
The delete from t
Where the rowid & lt;
(select Max (rowid) from t t2
Where t.o bject_id=t2. Object_id
);
The second:
(note that the writing and the above is not entirely equivalent, it is deleted casually, retain the rowid biggest one,
Here are using analysis function, the retention time of the latest, since the above can be deleted casually, writing is sure to meet the requirements, including row_number must have the order BY keywords) :
The delete t
Where the rowid in (select rids
The from (select rowid rids,
Row_number () over (partition by object_id ORDER by created desc) rn
The from t)
Where an rn & gt; 1);
Decomposition of the second method:
The SELECT object_id, rowid rids,
Row_number () over (partition by object_id ORDER by created desc) rn
The from t; - use analysis function according to the creation time, object_id query the latest record
The SELECT object_id, rids, rn)
The FROM (select object_id, rowid rids,
Row_number () over (partition by object_id ORDER by created desc) rn
The from t)
Where an rn & gt; 1; - repeat query data rowid
The delete t
Where the rowid in (select rids
The from (select rowid rids,
Row_number () over (partition by object_id ORDER by created desc) rn
The from t)
Where an rn & gt; 1); - according to the rowid to delete,,
Finished,,