在 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"被指定了两次,都作为'DELETE' 并作为单独的数据源
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屋!