SQL server update from statement, sqlupdate

SQL server update from statement, sqlupdate

To update a table, use the where statement:

Copy codeThe Code is as follows:
UPDATE Ttest SET
StatusInd = 'active'
WHERE
Id = 123

Note:

The table name after the update statement. aliases cannot be enabled.
At this time, the id field is from the Ttest table (understandable)

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

Copy codeThe Code is as follows:
UPDATE Ttest SET
StatusInd = 'active'
FROM
Tparent parent
WHERE
Ttest.id = 123
AND Ttest. parentId = parent. id
AND parent. statusInd = 'active'

Note:

Aliases cannot be used for Ttest after update.
Locate the Ttest record to be modified and write it as follows: Ttest. id = 123

If you write id = 123 directly, you cannot tell whether it is the id of the Ttest table or the Tparent table.
This SQL statement indicates whether the statusInd attribute associated with its parent is 'active' if you want to modify its statusInd attribute'

In this case, the hierarchical relationship is defined using two tables (Ttest and Tparent.

However, if the design of a database table only uses a table (Ttest) to express the hierarchical relationship between data (Ttest. parentId = Ttest. id ),
How can we write data for the purpose? (If you want to modify its own statusInd attribute, you must check whether the statusInd attribute associated with its parent is also 'active ')

The implementation is as follows:

Copy codeThe Code is as follows:
UPDATE Ttest SET
StatusInd = 'active'
FROM
Ttest parent,
Ttest
WHERE
Ttest.id = 123
AND Ttest. parentId = parent. id
AND parent. statusInd = 'active'

Explanation:

The requirement is: modify its own statusInd attribute to determine whether the statusInd attribute associated with its parent is also 'active'
The table (Ttest) after update cannot be named as an alias!
The table after from is also a Ttest, but the Ttest record to be updated is not the same as the from Ttest record (to update the child, but to associate the child with its parent)

From must be followed by a Ttest without an alias to specify the record of the table, which is to be updated.
The table from join (Ttest) must have an alias to distinguish it from the update table (Ttest)