I have duplicate records that I am trying to remove in an Access DB using Jet SQL. I have managed to get a sub select query working that isolates the data points I am interested in using as a conditional call. However, I cannot get any variation of using the outer DELETE function to make it happen based on the sub-select output. The query looks like,
SELECT * FROM tbl1
INNER JOIN (
SELECT ID, MIN(ImportDate) AS mindate FROM (
SELECT ID, ImportDate
FROM tbl1
WHERE ID IN (SELECT ID FROM tbl1 GROUP BY ID HAVING COUNT(*) > 1))
GROUP BY ID) pp ON pp.ID = tbl1.ID AND pp.mindate = tbl1.ImportDate;
The above successfully selects all the data rows from tbl1 but when I wrap in
DELETE FROM tbl1 INNER JOIN(... or DELETE tbl1.* FROM tbl1 INNERJOIN(... it does now work. Here is final version which I feel should work,
DELETE tbl1.* FROM tbl1
INNER JOIN (
SELECT ID, MIN(ImportDate) AS mindate FROM (
SELECT ID, ImportDate
FROM tbl1
WHERE ID IN (SELECT ID FROM tbl1 GROUP BY ID HAVING COUNT(*) > 1))
GROUP BY ID) pp ON pp.ID = tbl1.ID AND pp.mindate = tbl1.ImportDate;
I have also tried,
DELETE * FROM tbl1
WHERE EXISTS(
SELECT * FROM tbl1
INNER JOIN (
SELECT ID, MIN(ImportDate) AS mindate FROM (
SELECT ID, ImportDate
FROM tbl1
WHERE ID IN (SELECT ID FROM tbl1 GROUP BY ID HAVING COUNT(*) > 1))
GROUP BY ID) pp ON pp.ID = tbl_IMEI_MASTER.ID AND pp.mindate = tbl_IMEI_MASTER.ImportDate);
The error says - 'Could not delete from specified tables' for the above, and for below - It returns all results when I click the view icon before executing.
I was also getting an error - 'Could not delete from specified tables' and so also tried,
DELETE DISTINCTROW
tbl1.*
FROM tbl1
INNER JOIN (
SELECT ID, MIN(ImportDate) AS mindate FROM (
SELECT ID, ImportDate
FROM tbl1
WHERE ID IN (SELECT ID FROM tbl1 GROUP BY ID HAVING COUNT(*) > 1))
GROUP BY ID) AS pp ON pp.ID = tbl1.ID AND pp.mindate = tbl1.ImportDate;
Any help would be greatly appreciated. Thank you!
