Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 144
  • Last Modified:

Remove duplicated records

In my table I have a big number of duplicated records.
How can I remove these so I have just unique records ?
A simple project that do this job ?

Regards,
Nick

P.S. I use a dbf table .
0
ginsonic
Asked:
ginsonic
  • 3
  • 3
  • 2
1 Solution
 
kretzschmarCommented:
use a query like
select distinct * from tablename

create a a temporary table with the resultset(batchmove)
empty the original table

insert the temporary table entries into the original-table (batchmove)

meikl ;-)
0
 
ginsonicAuthor Commented:
Can you send me a sample project ? I'm just an amateur programmer and is the first time when play with a database ( at this stage ).
0
 
ITugayCommented:
Nick, meikll :-)

there is a nested SQL statement that allow to delete "duplicate" records from the table. "Duplicate" - mean that every record has unique primary key but rest of fields are the same.

delete from sometable where id_field not in
(
select min(id_field) from sometable
group by field1, field2, field3
)

Nick, is it your case?

-----
Igor
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
kretzschmarCommented:
igor,
if there are an id-field,
then i don't have duplicate entries,
but if it depends on a part of fields,
then it would work.

nick, sample is in work

meikl ;-)
0
 
ITugayCommented:
yes meikl, this is why  I wrote duplicated as "duplicated".
0
 
ginsonicAuthor Commented:
Wait then at ginsonic@xnet.ro :)
0
 
kretzschmarCommented:
sample ready,
will not work if there are constraints
defined on the table
(master-detail-relationships)

unit db_del_duplicates_u;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  DBTables, Db, StdCtrls, bde;

type
  TForm1 = class(TForm)
    ComboBox1: TComboBox;
    ComboBox2: TComboBox;
    Button1: TButton;
    procedure ComboBox1Change(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}


//load aliases
procedure TForm1.FormCreate(Sender: TObject);
begin
  session.GetAliasNames(combobox1.Items);
end;

//load tables
procedure TForm1.ComboBox1Change(Sender: TObject);
var
  DriverName : String;
begin
  combobox2.text := ''; //clear
  DriverName := session.GetAliasDriverName(combobox1.text);
  if (DriverName = 'STANDARD') then
    session.GetTableNames(combobox1.text,'*.db?',true,false,combobox2.items)
  else
    session.GetTableNames(combobox1.text,'',false,false,combobox2.items);
end;

procedure do_delete_duplicates(aliasname, tablename : string);
const
  sqltext = 'select distinct * from %s';
var
  origtable, temptable : ttable;
  q : tquery;
begin
  q := tquery.create(nil);
  origtable := ttable.create(nil);
  temptable := ttable.create(nil);
  try
    //supply query
    q.DatabaseName := aliasname;
    q.SQL.text := format(sqltext,[tablename]);

    //supply temptable
    temptable.DatabaseName := aliasname;
    temptable.tablename := 'temptable';

    //suplly origtable
    origtable.DatabaseName := aliasname;
    origtable.tablename := tablename;

    //extract unique
    q.open;

    //save into temtable
    temptable.BatchMove(q,batcopy);

    //query not needed now
    q.close;

    //empty originaltable
    origtable.EmptyTable;

    //recopy unique-entries
    origtable.BatchMove(temptable,batAppend);

    //delete temptable
    temptable.DeleteTable;

  finally
    q.free;
    origtable.free;
    temptable.free;
  end;
end;

//sample usage
procedure TForm1.Button1Click(Sender: TObject);
begin
  do_delete_duplicates(combobox1.Text,combobox2.text);
end;

end.


i can send you source and app,
if further needed

meikl ;-)
0
 
ginsonicAuthor Commented:
Done . I have now with ~ 1000 records less , thanks .
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now