I created a query to delete some record in MariaDB
Query :
DELETE FROM HrAttLogsFormatted WHERE DateIn = '2019-04-10' AND Late != '' AND ( FingerId, CreatedDate ) IN ( SELECT FingerId, MAX( CreatedDate ) FROM HrAttLogsFormatted WHERE DateIn = '2019-04-10' AND Late != '' AND FingerId IN ( SELECT FingerId FROM HrAttLogsFormatted WHERE DateIn = '2019-04-10' AND Late != '' GROUP BY FingerId HAVING COUNT( FingerId ) > 1 ) GROUP BY FingerId )
Result :
Table ‘HrAttLogsFormatted’ is specified twice, both as a target for ‘DELETE’ and as a separate surce for data
But with the query I made unsuccessfully, is there a way to solve it?
Thank you in advance
[EDIT-SOLVED] It’s Solved with simply apply and query
DELETE t1 FROM HrAttLogsFormatted t1 INNER JOIN ( SELECT FingerId, MinCreatedDate FROM ( SELECT FingerId, MIN(CreatedDate) AS MinCreatedDate FROM HrAttLogsFormatted WHERE DateIn = '2019-05-03' AND Late != '' GROUP BY FingerId HAVING COUNT(FingerId) > 1 ) x ) t2 ON t1.FingerId = t2.FingerId AND t1.CreatedDate = t2.MinCreatedDate;
Advertisement
Answer
I would try writing this as a delete join:
DELETE t1 FROM HrAttLogsFormatted t1 INNER JOIN ( SELECT FingerId, MIN(CreatedDate) AS MinCreatedDate FROM HrAttLogsFormatted WHERE DateIn = '2019-04-10' AND Late != '' GROUP BY FingerId HAVING COUNT(FingerId) > 1 ) t2 ON t1.FingerId = t2.FingerId AND t1.CreatedDate = t2.MinCreatedDate;
If you really wanted to stick with your current query, you might be able to make it work by adding an additional subquery around the WHERE IN
clause:
AND (FingerId, CreatedDate) IN ( SELECT FingerId, MinCreatedDate FROM ( SELECT FingerId, MIN(CreatedDate) AS MinCreatedDate FROM HrAttLogsFormatted ... ) x )
But, I would use the version I gave, because it is simpler.