Using FireDac to update only 1 of a duplicate row (no primary key or unique field)

Question

I have an old application I am supporting that uses a Microsoft Access database. The original table design did not add primary keys to every table. I am working on a migration program that among other things is adding and filling in a new primary key field (GUID) when needed.

This is happening in three steps:

1. Add a new guid field with no constraints
2. Fill the field with new unique guids
3. Add the primary key constraints

My problem is setting the unique guids when the table has duplicate rows. Here is my code to set the guids.

  Query.SQL.Add('SELECT * FROM ' + TableName);
  Query.Open;

  while Query.Eof = false do
  begin
    Query.Edit;
    Query.FieldByName(NewPrimaryKeyFieldName).AsGuid := TGuid.NewGuid;
    Query.Post;

    Query.Next;
  end;

FireDac generates an update statement that contains a where clause with all the original fields/values in the row (since there is no unique field for it to use). However, because the rows are complete duplicates the statement still updates two rows.

FireDac correctly errors with this message

Update command updated [2] instead of [1] record.  

I can open up the database in Access and delete the duplicate records or assign them a unique guid by editing the table. I would like my conversion tool to automatically do this.

Is there some way to work with these duplicate rows in FireDac? Either to update just one at a time, or to delete just one of them?

Answer 1

In my opinion there is no way to do it with just one SQL Statement.

I would do this: 1. Copy the whole table without duplicates by using a new temp table

SELECT DISTINCT * FROM <TABLENAME>

2. Add the Keys
3. Delete old table content and copy new content from new table

Notes:

The DB Should be unavailable for everyone else for that Operation 2. Make BACKUP before