I have two tables with same structure that can have duplicated records, I want to identify which ones from table 2 already exists in table 1 and delete them from table 2. The following SELECT returns the duplicated records I want to delete. None of these tables have a primary key, so I need to do multiple 'ON' to identify unique records.
SELECT V.*
FROM table2 AS V
INNER JOIN table1 AS N
ON V.column1 = N.column1 AND V.column2 = N.column2 AND V.column3= N.column3;
Then I insert this as a subquery for the DELETE:
DELETE FROM table2
WHERE table2.column1 IN
(SELECT V.*
FROM table2 AS V
INNER JOIN table1 AS N
ON V.column1 = N.column1 AND V.column2 = N.column2 AND V.column3= N.column3);
When running this query I get the following error:
You have written a query that can return more than one field without using the reserved word EXISTS in the FROM clause of the main query. Correct the SELECT instruction of the subquery to request a single field.
I also tried this way, but it deletes all the records from table 2, not only the result of the subquery:
DELETE FROM table2
WHERE EXISTS
(SELECT V.*
FROM table2 AS V
INNER JOIN table1 AS N
ON V.column1 = N.column1 AND V.column2 = N.column2 AND V.column3= N.column3);
This is the first solution I came up with, but I'm wondering if it wouldn't be easier to do in MS Access inserting into table1 all the records from table2 that doesn't match, and then delete table2.
All sugestions will be appreciated :)
Take the advice of the error message and try using exists logic:
DELETE
FROM table2 t2
WHERE EXISTS (SELECT 1 FROM table1 t1
WHERE t1.column1 = t2.column1 AND
t1.column2 = t2.column2 AND
t1.column3 = t2.column3);
The problem with your current exists attempt is that the query inside the EXISTS clause always has a result set, and that result set is independent of the outer delete call. So, all records get deleted.