如何删除这些条件？

Question

如何从没有主键的表中删除满足几个条件的行？(实际上主键由每一列组成)

经过多次尝试后，我有了一段代码，它首先创建一个临时表，在其中插入我想删除的行，最后我尝试从原始表中删除那些与临时表相比符合条件的行。

但是它给了我一个关于不能链接几个部分标识符#temp.idCarga的错误

也许这很容易，但是我已经尝试了这么多时间，我无法正常集中注意力。

CREATE TABLE #TEMP 
(
    CC int,
    idCarga int, 
    Tipo nvarchar(50),
    Importe float,
    Bloque nvarchar(50),
    idsistema int
)

INSERT INTO #TEMP(CC,idCarga,Importe,Bloque,Tipo,IdSistema)
select distinct CI.CC,CI.idCarga,CI.Importe,CI.Bloque,CI.Tipo,CI.idSistema
from CONT_INGRESOS_InformeMayor CI
INNER JOIN 
(
    SELECT idCarga,CC,Bloque,Importe,TIpo
    FROM CONT_INGRESOS_InformeMayor
) CI2 
    ON CI.CC = CI2.CC 
    AND CI.Bloque = CI2.Bloque 
    AND CI.TIpo <> CI2.Tipo
WHERE CI.idCarga = @idCarga 
    AND CI2.idCarga = @idCarga 
    AND CI.Importe = 0

DELETE 
FROM CONT_INGRESOS_InformeMayor 
WHERE idCarga = #temp.idCarga 
    AND tipo = #temp.tipo 
    AND importe = #temp.importe 
    and bloque = #temp.bloque

Answer 1

另一方面，您可以尝试在不涉及临时表的情况下删除行:

DELETE FROM CI
from CONT_INGRESOS_InformeMayor CI
INNER JOIN 
(
    SELECT idCarga,CC,Bloque,Importe,TIpo
    FROM CONT_INGRESOS_InformeMayor
) CI2 
    ON CI.CC = CI2.CC 
    AND CI.Bloque = CI2.Bloque 
    AND CI.TIpo <> CI2.Tipo
WHERE CI.idCarga = @idCarga 
    AND CI2.idCarga = @idCarga 
    AND CI.Importe = 0

Answer 2

您的< code>DELETE缺少到< code>#temp表的< code>JOIN

DELETE t1
FROM CONT_INGRESOS_InformeMayor t1
INNER JOIN #temp t
    ON t1.idCarga = t.idCarga 
        AND t1.tipo = t.tipo 
        AND t1.importe = t.importe 
        and t1.bloque = t.bloque

Answer 3

您错过了< code>DELETE上的一个联接。试试这个:

DELETE _d
FROM CONT_INGRESOS_InformeMayor AS _d
INNER JOIN #temp AS _t
    ON _d.idCarga = _t.idCarga 
    AND _d.tipo = _t.tipo 
    AND _d.importe = _t.importe 
    AND _d.bloque = _t.bloque