在MariaDB中为"DELETE"指定了两次表 [英] Specified Twice Table for 'DELETE' in MariaDB

问题描述

我创建了一个查询以删除MariaDB中的某些记录

I created a query to delete some record in MariaDB

查询:

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 
)

结果:

表'HrAttLogsFormatted'被指定两次，两者均作为目标 删除"，并作为数据的单独来源

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?

提前谢谢

只需应用和查询即可解决

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;

推荐答案

我会尝试将其写为删除联接:

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;

如果您真的想继续使用当前查询，则可以通过在WHERE IN子句周围添加一个附加子查询来使其正常工作:

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.

这篇关于在MariaDB中为"DELETE"指定了两次表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！