Skip to main content

TSQL: Syntax of Using Table Aliases in an UPDATE Statement

In this post, we'll demonstrate using UPDATE statement with and without table alias. The syntax is slightly different as we will see in examples below.

UPDATE Statement without Table Alias​

In T-SQL, you can use the UPDATE statement without using table aliases. Here's how you can write the syntax to update a table directly:

Assuming you have a table called customers and you want to update a column in it based on a specific condition:

UPDATE Statement without Table Alias
UPDATE customers
SET notes = 'large order'
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE order_amount > 1000
);

In this example:

  • customers is the table you want to update.
  • The SET clause specifies the column you want to update (notes) and the new value (large order).
  • The WHERE clause filters the rows that you want to update based on a condition. In this case, it selects customer_id values from the orders table where the order_amount is greater than 1000.

This syntax does NOT use table aliases, but it directly refers to the table names.

UPDATE Statement with Table Alias​

If you want to use table aliases in your T-SQL UPDATE statement, here's how you can write it:

Assuming you have a table called customers and you want to update a column in it based on a condition involving another table orders, both using aliases:

UPDATE Statement with Table Alias
UPDATE c
SET c.notes = 'large order'
FROM customers AS c
WHERE c.customer_id IN (
    SELECT o.customer_id
    FROM orders AS o
    WHERE o.order_amount > 1000
);

In this example:

  • c is the alias for the customers table.
  • The SET clause specifies the column you want to update (notes) and the new value (large order).
  • The FROM clause indicates the table to update (customers AS c).
  • The WHERE clause filters the rows that you want to update based on a condition involving the alias of the customers table (c).
  • The subquery in the WHERE clause uses the alias o for the orders table and selects customer_id values where the order_amount is greater than 1000.

Summary​

Using aliases can make your SQL statements more concise and easier to read, you can consider using table aliases especially when dealing with more complex UPDATE statement involving multiple tables.