Delete statement with reference to an ID that is going to be deleted. What happens?

Transact-SQL
1

I have this delete statement:

DELETE REGISTRO WHERE ID in (Select a.ID
                             From Registro a
                                 left outer join Registro b on b.ID = @ID
                             Where DATEDIFF(S, b.ServerRecordTime, a.ServerRecordTime) <= 30 and 
                                   a.User = b.User and 
                                   a.Manual = 1)

I started to wonder if this would work, because once the record (ID = @ID) is deleted, then the query wouldn't return anything anymore.

Or does SQL first calculates the enwrapped query, returning an array of all the ID's that then are deleted in the next step?

2

Yes: SQL would first evaluate the enwrapped query, then use that array of IDs to do the DELETEs.

1 Like
3

but always be weary of the following when using IN

create table REGISTRO(id int)
create table Registro_due(antipasto int)

insert into REGISTRO
select distinct top 12  object_id From sys.objects


insert into Registro_due
select distinct top 12  object_id From sys.objects
--notice here Registro_due does not have a column name ID
--but it will go right ahead and delete all rows in REGISTRO  for you. SCARY!
DELETE REGISTRO WHERE ID in (Select ID    
                             From Registro_due a)
4

That is in fact scary, but will only occur if Registro_due has just one column defined. Seems that SQL allows "ID" as a generic column - name in this special context. Once you define a second column, then the "Column name or number of supplied values does not match table definition" - error is thrown.

Nevertheless it is rather irritating that this buggy behavior is not corrected.

Martin

5

No: being able to use "ID" in the subquery is NOT a bug and it's NOT a "generic" name.

It's the ID from the REGISTRO table. That is, it's the same as if you'd written:
(Select REGISTRO.ID From Registro_due a)
While that's not really a logical query, it's a valid one and SQL will run it and honor the results.

You can reference columns from the outer query in a subquery. Indeed, this is a critical requirement in some cases.

But it does mean you have to be careful. The best way to avoid issues is to always add a table / alias name before every column name in a subquery.

6

Thank you for this clarification. I generally know that you can reference the outer query in the inner, but was carried away by Yosiasz' warning. Now that I realized that that "ID" column belongs the inner query I feel much safer, because I never would write such a statement, which feels completly illogical.