UPDATE Statement (Modifying Data)

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]Code language: PHP (php)

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. (The FROM clause will be discussed later in this section.)

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.

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';Code language: PHP (php)

Example 7.12 modifies rows of a table with an expression.

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;Code language: PHP (php)

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.

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');Code language: PHP (php)

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.

USE sample;
UPDATE works_on
	SET job = NULL
		FROM works_on, employee
		WHERE emp_lname = 'Jones'
		AND works_on.emp_no = employee.emp_no;Code language: PHP (php)

Example 7.15 illustrates the use of the CASE expression in the UPDATE statement. (For a detailed discussion of this expression, refer section “CASE Expressions in SELECT Statements“.)

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
		ENDCode language: PHP (php)