Updating Existing Data

Team-Fly    

 
Sams Teach Yourself SQL in 24 Hours, Third Edition
By Ronald R. Plew, Ryan K. Stephens
Table of Contents
Hour 5.  Manipulating 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 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 :

 graphics/syntax_icon.gif 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.

 graphics/input_icon.gif  UPDATE ORDERS_TBL   SET QTY = 1   WHERE ORD_NUM = '23A16';  graphics/output_icon.gif 1 row updated. 

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

 graphics/input_icon.gif  UPDATE ORDERS_TBL   SET QTY = 1;  graphics/output_icon.gif 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.

graphics/cautions_icon.gif

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:

 graphics/syntax_icon.gif 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.

 graphics/input_icon.gif  UPDATE ORDERS_TBL   SET QTY = 1,   CUST_ID = '221'   WHERE ORD_NUM = '23A16';  graphics/output_icon.gif 1 row updated. 

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

graphics/note_icon.gif

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.



Team-Fly    
Top
 


Sams Teach Yourself SQL in 24 Hours
Sams Teach Yourself SQL in 24 Hours (5th Edition) (Sams Teach Yourself -- Hours)
ISBN: 0672335417
EAN: 2147483647
Year: 2002
Pages: 275

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net