1

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;
Paulo Boaventura
  • 1,365
  • 1
  • 9
  • 29
Aldan
  • 674
  • 9
  • 23
  • I can give you a workaround right now, but I would ask that you please clearly tell us the logic happening here. My guess is that we can rewrite your query in a simpler way. – Tim Biegeleisen May 06 '19 at 03:31
  • Ok, thank you in advance. In this case I have the same 2 data where the first data is created at the beginning and the second data is created at the end ... from both of these data I want to delete the second data where this data was made last @TimBiegeleisen – Aldan May 06 '19 at 03:38

1 Answers1

1

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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Hi Tim, i try your sql version but something wrong with the query.. please check in https://ibb.co/J3CDqbs – Aldan May 06 '19 at 03:43
  • Hahaha, I didn't see before that typos were in `CreateDate` and I tried it again but the results remained the same – Aldan May 06 '19 at 03:53
  • Try it again please. – Tim Biegeleisen May 06 '19 at 03:55
  • Results remained the same, It seems the problem is with `HAVING COUNT`, for `MariaDB1 itself in some articles I read `HAVING COUNT` in `MariaDb` Format: `HAVING COUNT (* Field *)` not support `HAVING (COUNT FingerId)` – Aldan May 06 '19 at 04:01
  • Yeah, I tried to replace `HAVING (COUNT FingerId)` to `HAVING COUNT (FingerId)` and it's worked.. Thank You Very Much @TimBiegeleisen for the explanation and simpler the query – Aldan May 06 '19 at 04:08