Database Web Apps
Queries Cursors Views
Writing Logic Queries
Dynamic SQL Statements
Retrieving Data Multiple Tables
Multiple Table Retrieval
Alternate Queries Aliases
Inserting Data Transact SQL
SQL Statement Punctuation
Inserting Values from another Table
Updating Values from differentr Table
Indexing Query Strategies
Full Text Searching
Defining Full Text Indexes
Implement Full Text Search
Update on Transact-SQL
Line 1 indicates which table is going to be updated by the statement. In this case, the Employee table will be updated
Line 2 shows the column that will be updated in the Employee table and the new value that it will be set to.
In the above statement, the CurrentSalary column is going to be updated to the current value of the CurrentSalary column, multiplied by 1.1
The Employee and SalaryHistory tables are joined with an outer join, taking all values from the Employee table. The alias names, E and S are used for the Employee and SalaryHistory tables respectively.
Line 3 is very important because it does many things. It indicates that the criteria for determining which rows are updated in the Employee table are going to come from the Employee table and the SalaryHistory table.
The ON and AND statements indicate which values will be used to join the tables together. Lines 4 and 5 are used to indicate which values are to be used in joining the tables together.
The WHERE clause limits the number of records that are updated in the Employee table. Line 6 is also very important. It uses a function in the WHERE clause to limit the number of records that are updated in the Employee table. The function, called DATEDIFF, returns the number of time units specified between two dates. The time unit is days, indicated by dd. The two dates are the 1) RaiseDate column in the SalaryHistory table and 2) the current date generated by GETDATE. The number of days returned from this function are compared to 365. Therefore, only records with a RaiseDate of more than a year (or 365 days).
The DATEDIFF function returns the number of 'time units' specified between two dates.
Because we are using an outer join to return all data from the Employee table, if there is no corresponding record in the SalaryHistory table, the DATEDIFF function will return NULL. Therefore, we need to cover this case, too. This is done in Line 7.