how to search between dates when using an inner join to delete

Question

hello i am trying to search for duplicates in a table within a period using this snippet.

FROM
    table WHERE Start_Date BETWEEN '2018-07-01' AND '2018-07-31'
GROUP BY Policy_Number
HAVING COUNT(Policy_Number) > 1;

this produces all the duplicates records in the table within the required dates with their counts.

Now i am trying to delete those duplicate records using this snippets using this snippet i have also found online

DELETE t1 FROM table t1
        INNER JOIN
    table t2 
WHERE 
    t1.id < t2.id AND t1.Policy_Number = t2.Policy_Number AND Start_Date BETWEEN '2018-07-01' AND '2018-07-31';

but i keep getting this error

Column 'Start_Date' in where clause is ambiguous

Please how can i correct this to delete the duplicates i want removed thanks!!

Answer 1

write like this way t1.Start_Date its work

Try running as a query previous executing your select:

SET SQL_BIG_SELECTS=1;

DELETE t1 FROM table t1
        INNER JOIN
    table t2 
WHERE 
    t1.id < t2.id AND t1.Policy_Number = t2.Policy_Number AND t1.Start_Date BETWEEN '2018-07-01' AND '2018-07-31';

Because you create t1 and t2 using same table table so both have start_date and thats why its give Column 'Start_Date' in where clause is ambiguous error