Data Manipulation Languagde - UPDATE Statement

Update statement in SQL Server is used for modifying the data, which is a available in a specific table.

Syntax:

UPDATE TABLENAME SET COLUMNNAME =NEWVALUE [, COLUMNNAME= NEWVALUE….]

Example:

Write a query to modify (increase) the salaries of all the employees in EMP table

UPDATE EMP SAL =SAL+1000

The above statement (modifies) increases all employees salaries by 10000

This type of updating operation is called HIGH LEVEL UPDATE operation.

Example:

Write a Query to modify (increase) the salaries of all employees who are working under 10th department.

UPDATE EMP SET SAL = SAL+500 WHERE DEPTNO=10

Write a Query to modify the salary of an employ whose employ number 11 and who is working under 20th department.

UPDATE EMP SET SAL= SAL+300 WHERE EMPNO=11 AND DEPTNO=20

CASE Statement:

In SQL server CASE statement is used for evaluating multiple conditions on a specific column. It is mostly associated with update statement.

Syntax:

UPDATE TABLENAME SET COLUMN NAME= CASE
WHEN CONDITION1 THEN RESULT1
WHEN CONDITION2 THEN RESULT2
-------------------------
-------------------------
WHEN CONDITIONN THEN RESULTN
[ELSE RESULT]
END

Example:

Write a query to arrange BONUS column values according to the following specifications.

1. SAL <5000 BONUS 1000
2. SAL >5000 and <=10000 BONUS 2000
3. SAL > 10000 BONUS 3000

UPDATE EMP SET BONUS=CASE

WHEN SAL<=5000 THEN 1000

WHEN SAL>5000 AND SAL<=10000 THEN 2000

WHEN SAL>10000 THEN 3000 (OR) ELSE 3000

END

The above statement arranges BONUS column values according to the salaries.

Data Manipulation Languagde - UPDATE Statement