mardi 24 mars 2015

SQL UPDATE and FROM clause. How to know which table is being updated?

I am studying the various effects of the SQL UPDATE command.


In reading the MSDN official definition, they mention an example of an UPDATE of the same table in both the UPDATE and FROM clause


ie:



USE AdventureWorks;
GO
UPDATE Sales.SalesPerson
SET SalesYTD = SalesYTD + SubTotal
FROM Sales.SalesPerson AS sp
JOIN Sales.SalesOrderHeader AS so
ON sp.SalesPersonID = so.SalesPersonID
AND so.OrderDate = (SELECT MAX(OrderDate)
FROM Sales.SalesOrderHeader
WHERE SalesPersonID =
sp.SalesPersonID);
GO


It's obvious the example is updating the SalesYTD from the Sales.SalesPerson table because the same table is linked in the FROM clause but with an ALIAS, sp.


That's easy, but what if it did not have an alias?


ie:



UPDATE Sales.SalesPerson
SET SalesYTD = SalesYTD + SubTotal
FROM Sales.SalesPerson
...


How would the UPDATE know which instance of the table (row) it's updating? My question is specifically to know which "instance" of the table it's going to update, the one mentioned in the UPDATE statement or the one mentioned in the FROM clause?


Any clarification on the above example from MSDN would be appreciated.


Thanks


UPDATE Could the original example have been written thus ...?



UPDATE sp
SET sp.SalesYTD = sp.SalesYTD + SubTotal
FROM Sales.SalesPerson AS sp
...

Aucun commentaire:

Enregistrer un commentaire