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

I have this delete statement:

                             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?

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

1 Like

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!
                             From Registro_due a) 

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.


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.

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.