Can I delete entries from two tables in one statement?

user_6480658 0
Viewed 141

I have to remove a row from each of two tables, they're linked by an ID but not with a proper PK - FK relationship (this db has NO foreign keys!)

The tables have a supposed 1-1 relationship. I don't know why they weren't just put in the same table but I'm not at liberty to change it.

People

PersonId | Name | OwnsMonkey
----------------------------
    1       Jim       true
    2       Jim       false
    3       Gaz       true

Info

PersonId |    FurtherInfo
-----------------------------
    1       Hates his monkey
    2        Wants a monkey
    3       Loves his monkey

To decide what to delete, I have to find a username and whether or not they own a monkey:

Select PersonId from People where Name = 'Jim' and OwnsMonkey = 'false'

SO I'm doing two separate statements using this idea, deleting from Info first and then from People

delete from Info where PersonId = (Select PersonId from People where Name = 'Jim' and OwnsMonkey = 'false');

delete from People where PersonId = (Select PersonId from People where Name = 'Jim' and OwnsMonkey = 'false');

I found a promising answer here on StackOverflow

delete      a.*, b.*
from        People a
inner join  Info b
where       a.People = b.Info
            and a.PersonId = 
            (Select PersonId from People where Name = 'Jim' and OwnsMonkey = 'false')

But it gives a syntax error in Sql Server (2012), I tried it without alias' too, but it doesn't seem possible to delete on two tables at once

sql-server-2012 sql-server sql
1 Answers

Can I delete entries from two tables in one statement?

No. One statement can delete rows only from one table in MS SQL Server.

The answer that you refer to talks about MySQL and MySQL indeed allows to delete from several tables with one statement, as can be seen in the MySQL docs. MS SQL Server doesn't support this, as can be seen in the docs. There is no syntax to include more than one table in the DELETE statement in SQL Server. If you try to delete from a view, rather than a table, there is a limitation as well:

The view referenced by table_or_view_name must be updatable and reference exactly one base table in the FROM clause of the view definition.

I was hoping to avoid two separate statements on the off-chance the second doesn't work for whatever reason, interrupted - concurrency really, I guess the TRY/CATCH will work well for that.

This is what transactions are for. You can put several statements in a transaction and either all of them would succeed, or all of them would fail. Either all or nothing.

In your case you not just can, but should put both DELETE statements in a transaction.

TRY/CATCH helps to process possible errors in a more controlled way, but the primary concept is "transaction".

BEGIN TRANSACTION

delete from Info where PersonId = (Select PersonId from People where Name = 'Jim' and OwnsMonkey = 'false');

delete from People where PersonId = (Select PersonId from People where Name = 'Jim' and OwnsMonkey = 'false');

COMMIT

I highly recommend to read a great article Error and Transaction Handling in SQL Server by Erland Sommarskog.

If you try to be tricky, like this:

WITH
CTE
AS
(
    SELECT
        Info.PersonId AS ID1, People.PersonId AS ID2
    FROM
        Info
        INNER JOIN People ON Info.PersonId = People.PersonId
)
DELETE FROM CTE
WHERE ID1 = 1;

You'll get an error:

View or function 'CTE' is not updatable because the modification affects multiple base tables.

Or like this:

WITH
CTE
AS
(
    SELECT
    PersonId
    FROM Info

    UNION ALL

    SELECT
    PersonId
    FROM People
)
DELETE FROM CTE
WHERE PersonId = 1;

You'll get another error:

View 'CTE' is not updatable because the definition contains a UNION operator.

user_41160170
Related