Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies®

III.7.3. Modifying Data in your Database

The UPDATE statement is used to modify existing data in a table or a view.

Some general rules and guidelines to follow when adding data with the UPDATE statement are

• NULL data: If a column is specified as NOT NULL then the UPDATE statement can't change the data to NULL.

• Data type: The data type of the modified data must match the data type of the column. In other words, if you try to modify numeric data with text data, the UPDATE will fail.

• Constraints or rules: If the UPDATE violates a constraint or rule on the table, the UPDATE will fail. For example, if a constraint specifies that a credit rating column can have values between 1 and 5 only, then an UPDATE with a value of 0 will fail.

The basic syntax of the UPDATE statement is

UPDATE tableName
SET columnNameA = value, columnNameB = value,,,
WHERE columnName = value

The column values are set to their new values in the SET clause. Notice that multiple columns can be changed in a single UPDATE statement. Additional column names and their new values are separated with a comma.

A WHERE clause is used to filter the data. In other words, the WHERE clause is used to identify specifically which rows to update. If the WHERE clause is omitted, all the rows are updated the same way.

Use the following steps to modify data in a table with the UDPATE statement:

1. Launch SQL Server Management Studio.

   Choose StartAll ProgramsMicrosoft SQL Server 2008SQL Server Management Studio.

2. Create a new query window by ...