Bill-
Man, I miss A.D. That works because the correlation in the subquery forces the DELETE to re-evaluate on every row. On the lowest row, there are no other matches where the ID is <= the current row, so Count returns 1. The next higher row returns 2, so it deletes that row.
Very slick.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Jan 27, 2015, at 4:44 PM, wrmosca@comcast.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Robin
A.D. Tejpal, our retired friend in India came up with this one years ago. You have to match up all the fields that indicate a duplicate record. The lowest ID is kept as the "good" record.
DELETE *
FROM T_Data
WHERE (SELECT Count(*)
FROM T_Data AS T
WHERE Nz(T.F1, "") = Nz(T_Data.F1, "")
AND Nz(T.F2, "") = Nz(T_Data.F2, "")
AND Nz(T.F3, "") = Nz(T_Data.F3, "")
AND Nz(T.F4, "") = Nz(T_Data.F4, "")
AND T.ID <= T_Data.ID) > 1;
Bill Mosca
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (13) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar