How to delete multiple rows in diferent tables?

Question

I have a little problem, how could I improve my QUERY, I want to delete multiple rows from multiple tables using the foreign key with this :

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;

But, when i execute this query, return this:

Incorrect syntax near @ID

Please, I need corrections

score 1 · Accepted Answer · answered Apr 25 '18 at 06:40

1

We can use joins with delete statement to do this -

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;

If you want to delete foreign key tables data automatically, you can look into On Delete Cacade

answered Apr 25 '18 at 06:40

DatabaseCoder

2,004
2
12
22

The structure is correct, but the first record to delete is in the SCOUTL.RegistroUsuario table. And if I used On Cascade Delete, this delete all references. In this case SCOUTL.RegistroUsuario use four references. – Mateo Rodríguez Apr 25 '18 at 22:38
I want first delete the record in the SCOUTL.RegistroUsuario table and then the SCOUTL.EvidenciaPorPractica. – Mateo Rodríguez Apr 25 '18 at 22:44
You want to delete first from `SCOUTL.RegistroUsuario` table then `SCOUTL.EvidenciaPorPractica` table but if there is `primary key-foreign key` relation between these tables then SQL engine will not allow us to delete data first from primary table. – DatabaseCoder Apr 26 '18 at 05:15

score 0 · Answer 2 · answered Apr 25 '18 at 06:39

Try this

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;

score 0 · Answer 3 · answered Apr 25 '18 at 06:44

Try This:

 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;

How to delete multiple rows in diferent tables?

3 Answers3