CODEWITHSUNDEEP
sqlsql-serverstored-procedurestree
zooney
zooney

Reputation: 339

Stored procedure to delete a row as well as all foreign references within the same table

I have table AB with columns A , B and is_active where A is primary key and B references column A e.g the entries are (1,null,1),(2,1,1),(3,null,1),(4,2,1),(5,3,1),(6,5,1) and so on. So if I delete entry with primary key 1, then rows should get updated to (1,null,0),(2,1,0),(3,null,1),(4,2,0),(5,3,1) i.e. is_active is set to zero on deletion for all the referenced rows.I need to write a stored procedure for it.Its something like a tree deletion but what would be stored procedure for it?

Upvotes: 0

Views: 128

Answers (1)

peterm
peterm

Reputation: 92785

If you're using SQL Server then your procedure might look like

CREATE PROCEDURE recursive_delete(@id INT)
AS 
BEGIN
  SET NOCOUNT ON;
  WITH tree(a, b, is_active) AS
  (
    SELECT a, b, is_active
      FROM table1
     WHERE a = @id
    UNION ALL
    SELECT s.a, s.b, s.is_active 
      FROM tree t JOIN table1 s
        ON t.a = s.b
  )
  UPDATE s
     SET s.is_active = 0
    FROM table1 s JOIN tree t
      ON s.a = t.a
END

Here is SQLFiddle demo

Upvotes: 1

Related Questions