CRUD Statements  «Prev 

Update on Transact-SQL

Line 1 indicates which table is going to be updated by the statement. In this case, Employee will be updated.
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
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

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. These two tables are joined with an outer join, taking all values from the Employee table. Line 3 also indicates the alias names, E and S for the Employee and SalaryHistory tables respectively.
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 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 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).

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.
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.