代码之家  ›  专栏  ›  技术社区  ›  Mateo Rodríguez

如何删除不同表中的多行?

  •  0
  • Mateo Rodríguez  · 技术社区  · 7 年前

    我有一个小问题,如何改进我的查询,我想使用外键从多个表中删除多行,如下所示:

    CREATE PROCEDURE SCOUTL.DeleteRegistroUsuario(@ID INT)
    AS
         BEGIN
             DECLARE @Evidencia AS UNIQUEIDENTIFIER;
             SET @Evidencia =
    (
        SELECT e.IdEvidenciaPorPractica
        FROM SCOUTL.EvidenciaPorPractica e
             INNER JOIN SCOUTL.RegistroUsuario r ON e.IdEvidenciaPorPractica = r.IdEvidenciaPorPracticaFK
        WHERE r.IdRegistroUsuario = @ID
    );
             DELETE FROM SCOUTL.RegistroUsuario
             WHERE IdRegistroUsuario = @ID;
             DELETE FROM SCOUTL.EvidenciaPorPractica
             WHERE IdEvidenciaPorPractica = @Evidencia;
         END;
    

    但是,当我执行此查询时,返回以下内容:

    Incorrect syntax near @ID

    拜托,我需要更正

    3 回复  |  直到 7 年前
        1
  •  1
  •   DatabaseCoder    7 年前

    我们可以使用 joins 具有 delete 执行此操作的语句-

    CREATE PROCEDURE SCOUTL.DeleteRegistroUsuario(@ID INT)
    AS
    BEGIN
        DELETE e
        FROM SCOUTL.EvidenciaPorPractica e
        INNER JOIN SCOUTL.RegistroUsuario r ON
             e.IdEvidenciaPorPractica = r.IdEvidenciaPorPracticaFK
        WHERE r.IdRegistroUsuario = @ID
    
        DELETE FROM SCOUTL.RegistroUsuario
        WHERE IdRegistroUsuario = @ID
    END;
    

    如果要自动删除外键表数据,可以查看 On Delete Cacade

        2
  •  0
  •   Jayasurya Satheesh    7 年前

    试试这个

    CREATE PROCEDURE dbo.DeleteRegistroUsuario
    (
        @ID INT
    )
    AS
    BEGIN
    
        DELETE E
           FROM SCOUTL.EvidenciaPorPractica e
              INNER JOIN SCOUTL.RegistroUsuario r 
                 ON e.IdEvidenciaPorPractica = r.IdEvidenciaPorPracticaFK
           WHERE r.IdRegistroUsuario = @ID
    
        DELETE FROM SCOUTL.RegistroUsuario WHERE IdRegistroUsuario = @ID;
    
    END;
    
        3
  •  0
  •   Kaval Patel    7 年前

    尝试以下操作:

     CREATE PROCEDURE SCOUTL.DeleteRegistroUsuario(@ID INT)
    AS
    BEGIN
    
        DELETE e FROM SCOUTL.EvidenciaPorPractica AS e
        INNER JOIN SCOUTL.RegistroUsuario r ON e.IdEvidenciaPorPractica = r.IdEvidenciaPorPracticaFK
        WHERE r.IdRegistroUsuario = @ID
    
        DELETE FROM SCOUTL.RegistroUsuario
        WHERE IdRegistroUsuario = @ID;
    
    END;