Lesson 5	Using UPDATE
Objective	Modify existing records using the UPDATE statement.

Modify existing Records using the UPDATE statement

Data in the VirtualBookShelf.com Web site will change on a frequent basis. New customers will be added while others will become inactive or customer information will need to be changed if a customer moves. Book inventory information will change as the inventory fluctuates or when a book status changes from the regular price to sale price.
The UPDATE statement is used to modify rows in a table. A single UPDATE statement can modify all rows in a table or only selected rows.

Clauses used with UPDATE
Like the SELECT statement, the UPDATE statement uses clauses to specify columns and rows. These UPDATE clauses are:
1. SET: allows you to update single or multiple columns
2. WHERE: allows you to update specific rows just as with the SELECT statement
The following series of images describes how to construct the UPDATE statement syntax using the WHERE and SET clauses:

1) This part of the UPDATE statement specifies the tablet that will be updated. — 1) This part of the UPDATE statement specifies the table that will be updated

The SET clause specifies the column names and new data values to store in the column. — 2) The SET clause specifies the column names and new data values to store in the column

3) Change the values in the specified columns in all the rows in the table.

4) When a customer purchases a book from the VirtualBookShelf.com website, the application should decrease the Qty column by the number purchased.

The SET clause specifies the column name and new value. In this example,
1 is subtracted from the current value of Qty and the result is stored in the Qty column. — 5) The SET clause specifies the column name and new value. In this example, 1 is subtracted from the current value of Qty and the result is stored in the Qty column.

6)Where clause specifies the row. This statement reduces the Qty column by one in each row where the book title is.

7) This example assumes only one book of the specified title was purchased, so setting the new value with the expression Qty = Qty -1 is appropriate.

8) Portion of the BookTable before the UPDATE statement is executed.

9) Here is the BookTable after the UPDATE statement has executed.

Using the Update statement in SQL - Server

UPDATE table_name
 SET column1=value1,column2=value2,...
 WHERE some_column=some_value;

SQL also lets you explicitly set values with a literal, such as Qty = 5. This might be used if you want to initialize columns in new rows to an initial value, or reset columns to a specific value. In the next lesson, the addition of rows to a table will be discussed.