使用InnerJoin Firebird删除STATEMENT [英] Delete STATEMENT with InnerJoin Firebird

问题描述

我做了这个选择语句，以查找要删除的重复行.虽然我只是简单地将SELECT TO DELETE更改为并删除行，但没有删除.

I made this select statement to find duplicated rows I want to delete. I though that I simply change SELECT TO DELETE and It'd delete rows, but it didn't.

这是select语句:

This is select statement:

select * FROM MYCARD T1
INNER JOIN( SELECT IDCARD, YEAR, MONEY FROM MYCARD GROUP BY IDCARD, YEAR, MONEY HAVING COUNT(IDCARD) > 1 ) T2 ON
T1.IDCARD = T2.IDCARD AND T1.YEAR=T2.YEAR
AND T1.MONEY = T2.MONEY
WHERE T1.IDMONEY = 5 AND IDCARD=80
AND ID not in (select min(ID)
from MYCARD
WHERE IDMONEY=5
AND IDCARD=80
group by IDCARD, YEAR);

我想使用此删除操作:

DELETE FROM MYCARD T1
INNER JOIN( SELECT IDCARD, YEAR, MONEY FROM MYCARD GROUP BY IDCARD, YEAR, MONEY HAVING COUNT(IDCARD) > 1 ) T2 ON
T1.IDCARD = T2.IDCARD AND T1.YEAR=T2.YEAR
AND T1.MONEY = T2.MONEY
WHERE T1.IDMONEY = 5 AND IDCARD=80
AND ID not in (select min(ID)
from MYCARD
WHERE IDMONEY=5
AND IDCARD=80
group by IDCARD, YEAR);

但是它不起作用.有什么想法吗?

But it doesn't work. Any ideas?

我也尝试过:

DELETE FROM MYCARD
WHERE EXISTS (select * FROM MYCARD T1
INNER JOIN( SELECT IDCARD, YEAR, MONEY FROM MYCARD GROUP BY IDCARD, YEAR, MONEY HAVING COUNT(IDCARD) > 1 ) T2 ON
T1.IDCARD = T2.IDCARD AND T1.YEAR=T2.YEAR
AND T1.MONEY = T2.MONEY
WHERE T1.IDMONEY = 5 AND IDCARD=80
AND ID not in (select min(ID)
from MYCARD
WHERE IDMONEY=5
AND IDCARD=80
group by IDCARD, YEAR))

推荐答案

它不起作用，因为联接不是

It doesn't work, because joins are not part of the Firebird delete syntax.

例如，您需要使用已存在的删除

You need to use a delete with an exists, for example

delete from mycard t1
where exists (
   <a correlated subquery to identify the rows to delete>
)

或类似的方法来标识要删除的记录.底线是:所有必要的逻辑都必须在where子句中.

or a similar way to identify the records to delete. Bottomline is: all logic necessary needs to be in the where clause.

以示例为例，您需要执行以下操作:

Given the example, you need to do something like:

DELETE FROM MYCARD T1
WHERE T1.IDMONEY = 5 AND T1.IDCARD = 80
AND EXISTS (
    SELECT IDCARD, YEAR, MONEY 
    FROM MYCARD T2
    WHERE T2.IDCARD = T1.IDCARD 
    AND T2.YEAR = T1.YEAR
    AND T2.MONEY = T1.MONEY
    GROUP BY T2.IDCARD, T2.YEAR, T2.MONEY 
    HAVING COUNT(T2.IDCARD) > 1
)
AND T1.ID not in (
    select min(ID)
    from MYCARD
    WHERE IDMONEY = 5
    AND IDCARD = 80
    group by IDCARD, YEAR
)

您可能需要在现有的选择中添加其他AND T2.IDMONEY = 5(除非money和idmoney是同一事物).

You may need to add an additional AND T2.IDMONEY = 5 in the select in the exists (unless money and idmoney are the same thing).

这篇关于使用InnerJoin Firebird删除STATEMENT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！