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 UPDATE 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'; 1 row updated. The following example is identical to the previous example, except for the absence of the WHERE clause: UPDATE ORDERS_TBL SET QTY = 1; 11 rows updated. 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. | Extreme caution must be used when using the UPDATE statement without a WHERE clause. The target column is updated for all rows of data in the table if conditions are not designated using the WHERE clause. In most situations, the use of the WHERE clause with a DML command is appropriate. | 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 syntaxthere 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'; 1 row updated. A comma is used to separate the two columns being updated. Again, the WHERE clause is optional, but usually necessary. | The SET keyword is used only once for each UPDATE statement. If more than one column is to be updated, a comma is used to separate the columns to be updated. | |