Modification of a Table’s Contents in SQL Server: UPDATE Statement

The UPDATE statement modifies values of table rows. This statement has the following general form:

UPDATE tab_name

{ SET column_1 = {expression | DEFAULT | NULL} [,…n]

[FROM tab_name1 [,…n]];

[WHERE condition]

Rows in the tab_name table are modified in accordance with the WHERE clause. For each row to be modified, the UPDATE statement changes the values of the columns in the SET clause, assigning a constant (or generally an expression) to the associated column. If the WHERE clause is omitted, the UPDATE statement modifies all rows of the table. This means that if you have a table with 1 million rows, all rows will be updated if the WHERE clause is missing. (The FROM clause will be discussed later in this section.)

NOTE An UPDATE statement can modify data of a single table only. The UPDATE statement in Example 7.11 modifies exactly one row of the works_on table, because the combination of the columns emp_no and project_no builds the primary key of that table and is therefore unique. This example modifies the task of the employee, which was previously unknown or set to NULL.

Example 7.11

Set the task of employee number 18316, who works on project p2, to be ‘Manager’:

USE sample;

UPDATE works_on

SET job = ‘Manager’

WHERE emp_no = 18316

AND project_no = ‘p2’;

Example 7.12 modifies rows of a table with an expression.

Example 7.12

Change the budgets of all projects to be represented in English pounds. The current rate of exchange is 0.51£ for $1.

USE sample;

UPDATE project

SET budget = budget*0.51;

In the example, all rows of the project table will be modified because of the omitted WHERE clause. The modified rows of the project table can be displayed with the following Transact-SQL statement:

SELECT * FROM project;

The result is

Example 7.13 uses an inner query in the WHERE clause of the UPDATE statement. Because of the use of the IN operator, more than one row can result from this query.

Example 7.13

Due to her illness, set all tasks on all projects for Mrs. Jones to NULL:

USE sample;

UPDATE works_on

SET job = NULL

WHERE emp_no IN

(SELECT emp_no

FROM employee

WHERE emp_lname = ‘Jones’);

Example 7.13 can also be solved using the FROM clause of the UPDATE statement. The FROM clause contains the names of tables that are involved in the UPDATE statement. All these tables must be subsequently joined. Example 7.14 shows the use of the FROM clause.

This example is identical to the previous one.

NOTE The FROM clause is a Transact-SQL extension to the ANSI SQL standard.

Example 7.14

USE sample;

UPDATE works_on

SET job = NULL

FROM works_on, employee

WHERE emp_lname = ‘Jones’

AND works_on.emp_no = employee.emp_no;

Example 7.15 illustrates the use of the CASE expression in the UPDATE statement. (For a detailed discussion of this expression, refer to Chapter 6 and Example 6.50.)

Example 7.15

The budget of each project should be increased by a percentage (20, 10, or 5) depending on its previous amount of money. Those projects with a lower budget will be increased by the higher percentages.

USE sample;

UPDATE project

SET budget = CASE

WHEN budget >0 and budget < 100000 THEN budget*1.2

WHEN budget >= 100000 and budget < 200000 THEN budget*1.1

ELSE budget*1.05

END

Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.

Leave a Reply

Your email address will not be published. Required fields are marked *