Previous Page
Next Page

3.4. The UPDATE Command

Another common command used for setting/changing data values in a table is the UPDATE command. As with INSERT INTO .. SELECT, you often UPDATE more than one row. To examine how the UPDATE command works, we will use the tables we created in the previous section.

The general format for the UPDATE command is:

UPDATE TableName
SET fieldname...

For example, if you want to set all salaries in the table Emp2 to zero, you may do so with one UPDATE command:

UPDATE Emp2
SET sal = 0

Now, if you type:

SELECT *
FROM Emp2

You will get:

addr                 sal          empno
-------------------- ------------ -----------
123 4th St.          0.00         101
27 Shillingford      0.00         103
95 Oxford Rd         0.00         105
23 Pesterfield Cr    0.00         114
465 Easter Ave       0.00          95
 
(5 row(s) affected)

This UPDATE command sets all salaries in all rows of the Emp2 table to zero, regardless of previous values. As with any statement that affects all rows, this may be viewed as a dangerous command and caution should be observed.

It is often useful to include a WHERE clause in the UPDATE command so that values are set selectively. For example, if we assume that employee numbers are unique, we can UPDATE a specific employee from the Employee table with the following statement:

UPDATE Employee
SET    salary = 0
WHERE  employee_number=101

This query produces the following output:

names                address              employee_number salary
-------------------- -------------------- --------------- ------------
Joe Smith            123 4th St.          101                0.00
Pradeep Saha         27 Shillingford      103             3300.00
Sumit Kumar          95 Oxford Rd         105             1200.00
Joya Das             23 Pesterfield Cr    114             2290.00
Terry Livingstone    465 Easter Ave       95              3390.00
 
(5 row(s) affected)

Only employee number 101's row is updated. Once again, note that we do not use the quotes around 101, since employee_number is defined as an INT column (a numeric column). Quotes would have to be used around any character or string columns.


Previous Page
Next Page