CODEWITHSUNDEEP
sqlsql-servert-sql
Scott
Scott

Reputation: 2193

Stored procedure that deletes records by looping through Id's

So I am really confused on how to write this proc. Here is what needs to be done.

I have 3 tables laid out as follows:

tblObject {ObjectId, MasterId}

tblAnotherObject {ObjectId}

tblFurtherObject {ObjectId}

I need a proc that can delete the rows in 'tblAnotherObject' and 'tblFurtherObject' that contain an 'ObjectId' that does NOT have a matching 'ObjectId' in 'tblObject' that is paired with a passed in 'MasterId' to the proc.

I have been banging my head against a wall for hours today, and I have no clue how to write something like this...thank you so much ahead of time.

Upvotes: 0

Views: 266

Answers (1)

OMG Ponies
OMG Ponies

Reputation: 332531

I need a proc that can delete the rows in 'tblAnotherObject' and 'tblFurtherObject' that contain an 'ObjectId' that does NOT have a matching 'ObjectId' in 'tblObject' that is paired with a passed in 'MasterId' to the proc.

Use:

DELETE FROM TBLANOTHEROBJECT
 WHERE NOT EXISTS(SELECT NULL
                    FROM TBLOBJECT o
                   WHERE o.masterid = @masterid
                     AND o.objectid = TBLANOTHEROBJECT.objectid)

DELETE FROM TBLFURTHEROBJECT
 WHERE NOT EXISTS(SELECT NULL
                    FROM TBLOBJECT o
                   WHERE o.masterid = @masterid
                     AND o.objectid = TBLFURTHEROBJECT.objectid)

Say tblObject contains a row that has MasterId = 4 and ObjectId = 15. I want to delete all rows in the other two tables that have ObjectId = 15.

That's the opposite - use:

DELETE FROM TBLANOTHEROBJECT
 WHERE EXISTS(SELECT NULL
                FROM TBLOBJECT o
               WHERE o.masterid = @masterid
                 AND o.objectid = TBLANOTHEROBJECT.objectid)

DELETE FROM TBLFURTHEROBJECT
 WHERE EXISTS(SELECT NULL
                FROM TBLOBJECT o
               WHERE o.masterid = @masterid
                 AND o.objectid = TBLFURTHEROBJECT.objectid)

Upvotes: 2

Related Questions