Lesson 11	A warning about UPDATE
Objective	Use caution when using the SQL UPDATE Statement

Warning about the UPDATE Statement

If you omit the WHERE clause, you will update every single row in the table. Think about what would happen if you issued the statement from the last lesson, but left off the WHERE clause. It is good to experiment now with your test table, rather than finding out later what happens when you are working with real, live data. Without the WHERE clause, the UPDATE statement updates each and every row, setting all City column values to Seattle and destroying the integrity of your database. Always be extremely careful when using the UPDATE or DELETE statements.

Customer table consisting of 1) lastname 2) firstname 3) City 4) State — Customer table consisting of 1) lastname, 2) firstname, 3) City, 4) State

Once the update statement has been executed it cannot be undone. If you accidentally update a row, you will need to fix it manually by updating it with the prior values. In reality, you are not likely to know what you have updated incorrectly if the database table is sizable. It is better to be extra-cautious before you issue any UPDATE or DELETE statements than to have to try to pick up the pieces later. The next lesson shows you a more advanced use of the UPDATE statement.

Update SQL Data

As its name implies, the UPDATE statement allows you to update data in your SQL database. With the UPDATE statement, you can modify data in one or more rows for one or more columns. The syntax for the UPDATE statement can be shown as follows:

UPDATE <table name>
SET <set clause expression>
[{ , <set clause expression>} . . . ]
[ WHERE <search condition> ]

As you can see, the UPDATE clause and the SET clause are required, and the WHERE clause is optional. In the UPDATE clause, you must specify the name of the table (or view) that you are updating. In the SET clause, you must specify one or more set clause expressions, which I discuss in more detail later in this chapter. In the WHERE clause, as with the WHERE clause in a SELECT statement, you must specify a search condition. The WHERE clause works here in much the same way it does in the SELECT statement. You specify a condition or set of conditions that act as a filter for the rows that are updated. Only the rows that meet these conditions are updated. In other words, only rows that evaluate to true are updated.