[FIXED] What are the scoping rules for UPDATE when mixed with a JOIN?

Issue

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.

Solution

The documentation calls this out specifically:

FROM <table_source>

... snip ...

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.

You do not have to specify an alias for any table. If you do not then the table can obviously only be specified once.

But if you do give an alias then it depends: if the table exists in the query once then either with or without an alias is OK. Otherwise you must use the alias.

To be honest, as @AaronBertrand says, do yourself and everyone a favour and always use an alias on each table, and specify that alias in the UPDATE alias SET part.

Equally you should always use table aliases for each column reference in the joins or expressions. This is the same as you should do in any SELECT.

However, I don't think it's necessary to use it on the left-hand-side columns being assigned to, as these can only ever be from a single table/view/alias: the one directly after the keyword UPDATE, so adding table aliases is just verbiage.
_{In SQL dialects such as MySQL which do allow this then you should specify the table alias even on the left-hand-side.}

column_name

Is a column that contains the data to be changed. column_name must exist in table_or view_name.

So a recommended statement would look like this:

UPDATE u     -- use the alias mentioned below
SET
  assid = s.assid    -- lh-side doesn't need alias, right-side does
FROM ud u    -- alias all tables
JOIN sale s ON u.id = s.id    -- add table aliases to all columns in joins

Answered By - Charlieface

Answer Checked By - David Goodson (PHPFixing Volunteer)