37

It's possible to delete using join statements to qualify the set to be deleted, such as the following:

DELETE J
FROM Users U
inner join LinkingTable J on U.id = J.U_id
inner join Groups G on J.G_id = G.id

WHERE G.Name = 'Whatever'
and U.Name not in ('Exclude list')

However I'm interested in deleting both sides of the join criteria -- both the LinkingTable record and the User record on which it depends. I can't turn cascades on because my solution is Entity Framework code first and the bidirectional relationships make for multiple cascade paths.

Ideally, I'd like something like:

DELETE J, U
FROM Users U
inner join LinkingTable J on U.id = J.U_id
...

Syntactically this doesn't work out, but I'm curious if something like this is possible?

Improve this question
8
  • 3
    No, a DML statement can affect only one table. Your option is to remove one of the constraints (and potentially enforce it using a trigger instead). Commented Jul 9, 2013 at 3:07
  • 2
    I think you need to make it clearer in your question that you have foreign keys pointing in both directions. Commented Jul 9, 2013 at 3:07
  • @AaronBertrand: Thanks--that was the answer I was looking for: DML statements can affect only one table, and thus what I want is impossible. We'll have to figure out another way around this. If you write an answer to that effect I'll mark it. Commented Jul 9, 2013 at 8:18
  • @ bwerks As the Delete statement affect only one table you can use "deleted" pseudo table to delete the values from more than one tables, one by one by joining kindly refer to my answer below Commented Jul 10, 2013 at 7:23
  • @Aaron, not all DML stmts are single table operations. For example using OUTPUT clause in select stmt we can perform INSERT in 2 tables at the same time. For Delete yes unless Cascde is enable Delete is one table stmt in SQL server. Commented Nov 15, 2013 at 14:14

5 Answers 5

Reset to default
46

Nope, you'd need to run multiple statements.

Because you need to delete from two tables, consider creating a temp table of the matching ids:

SELECT U.Id INTO #RecordsToDelete
FROM Users U
   JOIN LinkingTable J ON U.Id = J.U_Id
...

And then delete from each of the tables:

DELETE FROM Users 
WHERE Id IN (SELECT Id FROM #RecordsToDelete)

DELETE FROM LinkingTable
WHERE Id IN (SELECT Id FROM #RecordsToDelete)
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I think the problem is a bi-directional foreign key, in which case separate statements won't work either (chicken + egg).
If that's the case, then perhaps dropping the constraint and adding it back after the delete runs could work as well.
I don't think EF - Code First allows you to disable / enable constraints on the fly, and it's probably not the optimal solution in a high concurrency environment anyway. If you're going to be turning off the constraint all the time, what's the point in ever turning it on?
4

The way you say is Possible in MY SQL but not for SQL SERVER

You can use of the "deleted" pseudo table for deleting the values from Two Tables at a time like,

 begin transaction;

 declare @deletedIds table ( samcol1 varchar(25) );

 delete #temp1
 output deleted.samcol1 into @deletedIds
 from #temp1 t1
 join #temp2 t2
 on t2.samcol1 = t1.samcol1

 delete #temp2
 from #temp2 t2
 join @deletedIds d
 on d.samcol1 = t2.samcol1;

 commit transaction;

For brief Explanation you can take a look at this Link

and to Know the Use of Deleted Table you can follow this Using the inserted and deleted Tables

Improve this answer

1 Comment

Did you mean, "The way you say is Possible in MY SQL but not for SQL SERVER"?
2

The only way I could think of is logically break the bi-directional foreign keys in a procedural way.

This approach can have huge impact to your application side if you don't have some flags for visualization state or status

Something like

  1. INSERT dummy not visible rows to Users (with something like Id = -1 for dummy values)

  2. Add to LinkingTable an alternative column to point back to Users, I'll call it U_ComesFrom

    ALTER TABLE LinkingTagble ADD U_ComesFrom_U_id INT DEFAULT(-1)

  3. Add FOREIGN KEY with a NOCHECK

    ALTER TABLE LinkingTable WITH NOCHECK
    FOREIGN KEY (U_ComesFrom_U_id)
    REFERENCES Users (Id) ;

  4. Add to Users column

    ALTER TABLE Users ADD MarkedForDeletion BIT NOT NULL DEFAULT(0)

Then your SQL would look like

BEGIN TRANSACTION
    UPDATE J
    SET U_Comes_From_U_id = U_ID, U_id = -1 -- or some N/R value that you define in Users
    FROM Users U
    inner join LinkingTable J on U.id = J.U_id
    inner join Groups G on J.G_id = G.id
    WHERE G.Name = 'Whatever'
    and U.Name not in ('Exclude list')

    UPDATE U
    SET MarkedForDeletion = 1
    FROM Users
    inner join LinkingTable J on U.id = J.U_ComesFrom_U_id 
    WHERE U_id > 0

    DELETE FROM LinkingTable 
    WHERE U_ComesFrom_U_id > 0

    DELETE FROM Users
    WHERE MarkedForDeletion = 1

COMMIT

This approach would impact the performance since each transaction would be at least 4 DML operations per bi-directional keys.

Improve this answer

Comments

1

Use TRY CATCH with Transaction

BEGIN TRANSACTION
BEGIN TRY
    DELETE from A WHERE id=1

    DELETE FROM b WHERE id=1

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
END CATCH

or you can also use Store procedure for same Using Stored Procedure With Transaction:

Improve this answer

Comments

1

If you are creating the foreign key through T-SQL you must append the ON DELETE CASCADE option to the foreign key:

Code Snippet 

ALTER TABLE <tablename>
ADD CONSTRAINT <constraintname> FOREIGN KEY (<columnname(s)>)
REFERENCES <referencedtablename> (<columnname(s)>)

ON DELETE CASCADE;
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.