Can an SQL DELETE statement followed by a WHERE NOT IN statement in a single transaction?

If I have the following SQL block (in SQL SERVER 2008 R2):

BEGIN
    BEGIN TRAN

    DELETE FROM dbo.fooData
    WHERE LastUpdate < DateAdd(hour, -1,GETUTCDATE())

    COMMIT

    BEGIN TRAN

    DELETE FROM dbo.barData
    WHERE SessionID NOT IN (SELECT sub.SessionId FROM dbo.fooData sub)

    COMMIT
    RETURN 0
END

I assume that I should make an explicit COMMIT between statements so that the deleted data from fooData is displayed in the second delete. It's right? Ideally, I would like all this to be in one transaction. Example:

BEGIN
    BEGIN TRAN

    DELETE FROM dbo.fooData
    WHERE LastUpdate < DateAdd(hour, -1,GETUTCDATE())

    DELETE FROM dbo.barData
    WHERE SessionID NOT IN (SELECT sub.SessionId FROM dbo.fooData sub)

    COMMIT
    RETURN 0
END

I am afraid that the second statement will not receive the first deleted data. Note that the return is because it is part of the stored procedure. I'm not interested in cascading deletions or attachments; I'm somewhat limited to this method.

+3
source share
2 answers

/ . .

, ( ) .

+3

.

, .

, , , . ( , ). , .

+2

All Articles