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!!

that's because it needs to know whether to read start_date from t1 or t2. It occurs in both (since they're both aliases of the same table). Just put the appropriate alias in front of it as per your requirement. BTW your INNER JOIN should have an ON clause really. — ADyson, Sep 07 '18 at 11:08

Bhargav Chudasama · Accepted Answer · 2018-09-07T11:27:24.863

2

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

edited Sep 07 '18 at 11:27

answered Sep 07 '18 at 11:10

Bhargav Chudasama

6,928
5
21
39

The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay – chap man Sep 07 '18 at 11:12
thats the error i get after running the corrected snippet u gave – chap man Sep 07 '18 at 11:13
check this link [here](https://stackoverflow.com/questions/943423/mysql-the-select-would-examine-more-than-max-join-size-rows) – Bhargav Chudasama Sep 07 '18 at 11:15
i update my answer and also give a link for you error check @chapman – Bhargav Chudasama Sep 07 '18 at 11:17
1

thanks but u need to add this ; after the first statement – chap man Sep 07 '18 at 11:26

Fahmi · Answer 2 · 2018-09-07T11:14:10.930

1

Define the alias before start_Date

SET OPTION 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'

edited Sep 07 '18 at 11:14

answered Sep 07 '18 at 11:09

Fahmi

37,315
5
22
31

The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay, thats the error i got – chap man Sep 07 '18 at 11:10
SET OPTION SQL_BIG_SELECTS = 1 use the before running the query – Fahmi Sep 07 '18 at 11:13
first execute SET OPTION SQL_BIG_SELECTS = 1 and then run the query – Fahmi Sep 07 '18 at 11:15
Unrecognized keyword. (near "OPTION" at position 4) Unexpected token. (near "SQL_BIG_SELECTS" at position 11) Unexpected token. (near "=" at position 27) Unexpected token. (near "1" at position 29) A new statement was found, but no delimiter between it and the previous one. (near "DELETE" at position 34) – chap man Sep 07 '18 at 11:16

score 0 · Answer 3 · answered Sep 07 '18 at 11:17

0

an example works SELECT table.Number FROM table INNER JOIN table2 ON table.ID = table2.ID WHERE checkInDate BETWEEN '2015-09-12' AND '2015-09-13';

answered Sep 07 '18 at 11:17

achref akrouti

575
1
6
21

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

3 Answers3