What are the scoping rules for UPDATE when mixed with a JOIN?-CodePudding

Whenever I read how to use a JOIN to UPDATE a table, the usage of aliases always throws me off just slightly. For example, take this code from here

UPDATE ud
SET assid = s.assid
FROM ud u
JOIN sale s ON u.id=s.id

It's clear to me that assid comes from the ud table on the line 1. What isn't clear to me is why ud needed to be aliased on lines 3 and 4. This isn't an isolated incident. For example, another answer on that same page is

update u
set u.assid = s.assid
from ud u
    inner join sale s on
        u.id = s.udid

which uses the alias u in a great many places.

This gives me my question: What exactly are the rules for what you do/don't need to alias when trying to use a JOIN to UPDATE? Directly quoting from any relevant docs would be appreciated, but not required.

CodePudding user response：

Technically, you don't need to use an alias prefix on a column reference:

in the left-hand side of SET since, logically, that column can't possibly come from any other table
if there are no column name conflicts that need disambiguation (and you know for sure you will never add columns to any of the involved tables that could become a conflict)

IMHO you shouldn't worry about any "rules" about when you can get away with not prefixing, and just always reference the alias a column belongs to. Why not just be clear and explicit always?

If you're looking for "official" rules, I think you'll be out of luck. Nothing about this could be found in this doc, though the first example has this, which is a little overboard IMHO (and doesn't use aliases):

UPDATE dbo.Table2   
SET dbo.Table2.ColB = dbo.Table2.ColB   dbo.Table1.ColB  
FROM dbo.Table2   
    INNER JOIN dbo.Table1   
    ON (dbo.Table2.ColA = dbo.Table1.ColA);

On this site, many long-standing canonical examples are fully explicit on all column references, e.g. this one.

CodePudding user response：

Note that none of this is mysterious if you use a CTE instead of UPDATE ... FROM, eg start with a SELECT that returns the rows you want to update

  select u.id, u.assid, s.assid new_assid 
  FROM ud u
  JOIN sale s ON u.id=s.id

. Examine and validate the data, then wrap it in a CTE and UPDATE it

with q as
(
  select u.id, u.assid, s.assid new_assid 
  FROM ud u
  JOIN sale s ON u.id=s.id
)
update q set assid = new_assid