title: DELETE duplicates with subquery from 2 tables in MS Access
body: <p>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.</p>
  
  <pre class="lang-sql prettyprint-override"><code>        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;
  </code></pre>
  
  <p>Then I insert this as a subquery for the DELETE:</p>
  
  <pre class="lang-sql prettyprint-override"><code>        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);
  </code></pre>
  
  <p>When running this query I get the following error:</p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <p>I also tried this way, but it deletes <strong>all the records</strong> from table 2, not only the result of the subquery:</p>
  
  <pre class="lang-sql prettyprint-override"><code>        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);
  </code></pre>
  
  <p>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.</p>
  
  <p>All sugestions will be appreciated :)</p>