0

The crux of my question is the following style of UPDATE statement in a trigger:

update ActualTableName 
    set X=Y 
from inserted

I would have thought that this is a syntax error... the table "ActualTableName" does not appear in the "from" clause.

So... is this just some sort of funky automatic alias "unwind" (i.e. it knows "inserted" is an alias for ActualTableName? This seems unlikely because of a later query (see below).

Or... is there something more... like the query is expanded to:

update ActualTableName 
    set X=Y 
from ActualTableName 
    cross join inserted

This also gets a bit stranger with a more complicated UPDATE that references both the inserted and deleted tables:

update ActualTableName
    set [... some assignments ...]
from 
    inserted 
        left outer join deleted
            on inserted.Id = deleted.Id 
               and inserted.SomeField > deleted.SomeField
where 
    inserted.Id <> ActualTableName.Id and

This query appears to be updating records in ActualTableName that are not part of the inserted table... and this leads me to think that the actual query is:

update ActualTableName
    set [... some assignments ...]
from 
    ActualTableName
        cross jon inserted 
        left outer join deleted
            on inserted.Id = deleted.Id 
               and inserted.SomeField > deleted.SomeField
where 
    inserted.Id <> ActualTableName.Id and

The books online are a bit opaque about this, and says this:

If the object being updated is the same as the object in the FROM clause and there is only one reference to the object in the FROM clause, an object alias may or may not be specified. If the object being updated appears more than one time in the FROM clause, one, and only one, reference to the object must not specify a table alias. All other references to the object in the FROM clause must include an object alias.

So... can anyone provide clarity as what is going on?

user505765
  • 529
  • 1
  • 8
  • 15
  • Does the first UPDATE in your question have any conditions? What follows the `and` in the "more complicated UPDATE's" `where` clause? Or perhaps you could explain what kind of business rule the trigger is supposed to handle? That might shed some light on why the statements are the way you see them (as might help in explaining how they work). – Andriy M Jul 22 '13 at 08:25

1 Answers1

-1

I'm not sure if you are aware of the fact that Inserted or Deleted in the above mentioned queries are not alias but special tables used by the SQL server to track insertion, update or deletion of data within triggers. Here is more information:

http://msdn.microsoft.com/en-us/library/ms191300.aspx

Sonam
  • 3,406
  • 1
  • 12
  • 24
  • These queries are in a trigger... so yes inserted and deleted (note the proper casing of these names is important) are special tables. if you examine the first update query its unclear to me why "ActualTableName" is used instead of "inserted". If you look at the second, more complicated query, it is very unclear to me what ActualTableName refers to. Inserted? Deleted? The real table? If the real table how is that joined to inserted/deleted? If inserted or deleted how does it determine which one? – user505765 Jul 18 '13 at 19:00
  • If we talk about the query which you have mentioned earlier(update ActualTableName set X=Y from inserted), here the user is trying to update the column "X" of table "ActualTableName" with the value which is recently inserted into the table on which trigger is defined. – Sonam Jul 18 '13 at 19:11