SQL Update statements use table aliases (multiple methods, classic)

Many people know how to use table aliases in select statements, but there may not be so many people who know how to use table aliases in SQL Update statements, the following describes how to use the table alias for this SQL Update statement for your reference.

The SQL Update statement can also be used as the table alias. The following describes how to use the table alias in the SQL Update statement. I hope you can learn about the SQL Update statement.

When writing an SQL script, you can use the table alias to greatly reduce the SQL code. At the same time, the table alias is also one of the solutions for multiple references to the same table. You should be familiar with using Table aliases in select:

select * from TableA as A inner join TableB as B on A.Key1 = B.Key1  

 

However, using Table aliases in SQL Update may not be known to many people.

update T     set T.Key1 = 'xxxx'    from TableA T  

 

These days, when writing an SQL Update statement script, you need to reference The same table object twice. If you directly reference TableA twice as follows, "The multi-part identifier 'tablea. index 'could not be bound "error. This is because the SQL engine cannot know whether TableA IN THE where clause refers to the table to be updated or the table after the from clause.

update TableA     set TTableA.NextKey = T.Key    from TableA T     where T.Index = TableA.Index + 1

 

If you do not use an alias for TableA after Update, you can only use the following methods.

 

update TableA     set TTableA.NextKey = T.Key    from     (     select * from TableA     )T      where T.Index = TableA.Index + 1  

 

Alias can be used to get a more concise Syntax:

update T1     set T1.NextKey = T2.Key    from TableA T1, TableA T2     whereT2.Index = T1.Index + 1