Updating Existing Data

Pre-existing data in a table can be modified using the UPDATE command. The UPDATE command does not add new records to a table, nor does it remove records—it simply updates existing data. The update is generally used to update one table at a time in a database, but can be used to update multiple columns of a table at the same time. An individual row of data in a table can be updated, or numerous rows of data can be updated in a single statement, depending on what's needed.

Updating the Value of a Single Column

The most simple form of the UPDATE statement is its use to update a single column in a table. Either a single row of data or numerous records can be updated when updating a single column in a table.

The syntax for updating a single column follows:

update table_name
set column_name = 'value'
[where condition];

The following example updates the QTY column in the ORDERS table to the new value 1 for the ORD_NUM 23A16, which you have specified using the WHERE clause.

							UPDATE ORDERS_TBL
							SET QTY = 1
							WHERE ORD_NUM = '23A16';
						

001 001 1 row updated.
002 002 

The following example is identical to the previous example, except for the absence of the WHERE clause:

							UPDATE ORDERS_TBL
							SET QTY = 1;
						

001 001 11 rows updated.
002 002 

Notice that in this example, 11 rows of data were updated. You set the QTY to 1, which updated the quantity column in the ORDERS_TBL table for all rows of data. Is this really what you wanted to do? Perhaps in some cases, but rarely will you issue an UPDATE statement without a WHERE clause.

Updating Multiple Columns in One or More Records

Next, you see how to update multiple columns with a single UPDATE statement. Study the following syntax:

update table_name
set column1 = 'value',
   [column2 = 'value',]
   [column3 = 'value']
[where condition];

Notice the use of the SET in this syntax—there is only one SET, but multiple columns. Each column is separated by a comma. You should start to see a trend in SQL. The comma is usually used to separate different types of arguments in SQL statements.

							UPDATE ORDERS_TBL
							SET QTY = 1,
							CUST_ID = '221'
							WHERE ORD_NUM = '23A16';

001 001 1 row updated.

A comma is used to separate the two columns being updated. Again, the WHERE clause is optional, but usually necessary.