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.
|