I dont understand this Update query can you explain-CodePudding

so here is a little ms sql server query:

update tblSaleReturnMain set sync=0
from tblSaleOrderMain s join tblSaleReturnMain r on r.ID=s.intReturnOrderId
 where s.sync=0

it updates my "tblSaleReturnMain" table just fine, also I wrote this query myself, but I dont know why it works. My question is, with all the many join-ed tables that I could reference after the "from" clause, and all the possible data that can be produced, how does this query know that the tblSaleReturnMain mentioned in "update tblSaleReturnMain .." is the same that is being filtered in join statement? Is that always like a protocol, like we mention a table before the "set" keyword and do not give it an alias, but then go on filtering/joining its data any way we like, and what remains in a resultset is what the "set" statement will apply to? My question is specifically for Update statements that have JOINS after the FROM keyword.

Also this question is not about "how to use join in Update statement", because I already did that successfully above.

CodePudding user response：

Yes, as long as you only use the target table once in the FROM SQL Server will assume it is the same table reference. From the docs (emphasis mine):

FROM <table_source>
Specifies that a table, view, or derived table source is used to provide the criteria for the update operation. For more information, see FROM (Transact-SQL).

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.

If you reference the same table more than once and try to update it using just the table name rather than the alias, you'll get an error along the lines of:

Msg 8154 Level 16 State 1 Line 2
The table 'tblSaleReturnMain ' is ambiguous.

You can reference the same table more than once, but if doing so you must use the alias as the table_or_view_name, e.g.

UPDATE Alias
SET    Col = 1
FROM   dbo.T1 AS Alias
       INNER JOIN dbo.T1 AS Alias2
           ON Alias.ID = Alias2.ID;

Examples on DB<>Fiddle

I personally always use the alias regardless of whether the full table reference would be ambiguous.

CodePudding user response：

SQL Server's UPDATE ... FROM syntax is non-standard and confusing.

Much better to write a CTE, examine the results, and then update the CTE, eg:

with q as
( 
  select r.sync
  from tblSaleOrderMain s 
  join tblSaleReturnMain r 
   on r.ID=s.intReturnOrderId
  where s.sync=0 
)
update q set sync = 0