SQL tips -- Update statement also uses table alias (table alias)

When writing SQL scripts, you can use the table alias to greatly reduce the SQLCodeThe 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 the table alias in update may not be known to many people.

Update tset T. key1 = 'xxxx' from tablea t

When writing an SQL update script these days, You need to reference the same table object twice. If you 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 tableaset tablea. nextkey = T. keyfrom tablea twhere T. Index = tablea. index + 1

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

Update tableaset tablea. nextkey = T. keyfrom (select * From tablea) twhere T. Index = tablea. index + 1

Alias can be used to get a more concise Syntax:

Update t1set t1.nextkey = t2.keyfrom tablea T1, tablea t2wheret2. Index = t1.index + 1

--Kevin Yang