The drill down of SQL Server update from statement _mssql

In general, update a table, using the WHERE statement:

Copy Code code as follows:

UPDATE Ttest SET
Statusind = ' ACTIVE '
WHERE
ID = 123

Attention:

The table name after the UPDATE statement, which is not enabled for aliases.
So the ID field at this point is from the TTest table (understandable)

However, if update, there are additional join table condition constraints, and the statement is as follows:

Copy Code code as follows:

UPDATE Ttest SET
Statusind = ' ACTIVE '
From
Tparent Parent
WHERE
Ttest.id = 123
and Ttest.parentid = Parent.id
and parent.statusind = ' ACTIVE '

Attention:

TTest after update cannot use aliases
So to locate, to modify the Ttest record, you must write this: Ttest.id = 123

If you write id=123 directly, you will not be able to tell whether the Ttest table or the Tparent table ID
This SQL statement means that if you want to modify your own Statusind property, you have to associate its parent's Statusind attribute as ' ACTIVE '

At this point, the hierarchical relationship is defined using two tables (TTest and tparent).

But if a database table is designed to represent a hierarchical relationship between data (Ttest.parentid = ttest.id), simply using a table (Ttest),
How to write, can achieve the purpose? (If you want to modify your own Statusind property, you have to associate its parent's Statusind attribute as ' ACTIVE ')

Implemented as follows:

Copy Code code as follows:

UPDATE Ttest SET
Statusind = ' ACTIVE '
From
Ttest Parent,
Ttest
WHERE
Ttest.id = 123
and Ttest.parentid = Parent.id
and parent.statusind = ' ACTIVE '

Explain:

Its requirement is to modify its own Statusind attribute, and to correlate its parent's Statusind attribute is also ' ACTIVE '
The table after update (Ttest) cannot, name alias!
The table following from is also Ttest, but the Ttest record to update is not the same as the From Ttest record (to update the child, but to from the associated parent)

From the back, you should write an alias-free Ttest to specify the record of this table, which is to be update.
For a From join (Ttest) table, you must have an alias that distinguishes the table from the update (Ttest)