SQL - Data modification - Part 9 - Creating surrogate table from original table to limit SQL update
|
Haziran 5, 2012 by sql tutorial
|
|
Article Information
This SQL Tutorial contains and tries to cover following subjects:
- Explanation of Referencing multiple tables while update in SQL
- Example to Referencing multiple tables in update query in SQL
Articles tries to provide answer to following questions
- How to update a column according to values in another table?
- How to update a column with referencing another table?
- How to update a column depending on a JOIN result set in SQL?
- How to use JOIN in sql update query?
- How to limit update query with filtering table?
Article covers followings indirectly:
- SQL update statement
- SQL update syntax
- SQL join command
Articles pre-requisites following information:
- General knowledge of SQL Server Management Studio
- General knowledge of SQL update statement
- General knowledge of SQL JOIN statement
Referencing another Table in SQL UPDATE query
In earlier article, we had covered UPDATE command. We also covered how to use a function in SQL update query to apply a global replace. SQL UPDATE can also be used with referencing other tables or with expressions. In this sql article, we will look into how to restrict update with referencing - dependant on JOIN type.
To be more practical, lets understand what does referencing another table mean. Assume that we have a table like customers, and a table that keeps orders of that customers. We need to update a column in customers table but not the all rows but the rows of customer data that relevant customer has not any order in orders table. For example, we have a column to indicate if that customer is active. In a single update query, we need to use a JOIN to orders to decide if that customer has any order.
Referencing a table works that way. Idea behind it, the referencing, is to create a mask from a JOIN result that the UPDATE itself is executed on that result set. Dont think the referencing as a clause, it is sort of a real surrogate of original table that UPDATE goes on. Surrogate table is a variation of original table, and update executes itself on that variation.
Using JOIN in SQL UPDATE query for referencing other table
Lets recall basic sql update command syntax:
update table1 set column1 = value
UPDATE customers SET customerName = 'Microsoft'
As above sql update syntax indicates, table name is added after update command. After the table name, we provide the SET and the column name. That was the basic form of SQL update command which we covered in earlier article. If we recall, that sql update command, in its basic form, updates entire column with the given value. If we use above command without a WHERE clause, all rows and customer names will be Microsoft. However, in our scenario, we need a JOIN to mask our original table that UPDATE will be executed on it. WHERE clause can limit rows of original table, for example can tell UPDATE query to not update customer names depend on ID or another column in same table. However, we want to update a column in that table according to customers order status in another table. Lets see it in the real example.
SQL Syntax for referencing is as follows:
update originalTable set column1 = value1 from originalTable
join referencedTable
on condition
where where clause
Example to reference another table in SQL UPDATE query
We have two table customers and orders as shown in below picture. activeAccount column is a new column we created. We want to add a status to the client depending on order status in orders table. If client has any order in orders table, status will be updated to the "active". Query is as follows:
As above sql update example indicates, rows which are referenced from another table only have been updated. UPDATE itself is executed in the result set of JOIN.
Data Layers
| Area: | programming \ languages \ tsql \ \ \ |
| Ref: | |
| Loc: | articles |
| Tags: | tsql |
|