Once a table is populated with data, you may need to update values in one or more of its rows. Column values in existing rows in a table can be updated with the UPDATE statement. The key to successful row updates is the creation of a well-constructed SET clause and WHERE expression. If the WHERE expression is not constructed correctly, the possibility of an update error is great.
Suppose all laptops in the PRODUCTS table have just been discounted by 20 percent and the new price is to take effect immediately. The update would compute the discounted product cost for “Laptop” computers only. For example, the discounted price for a laptop computer would be reduced to $2,720.00 from $3,400.00.
SQL Code
PROC SQL; UPDATE PRODUCTS SET PRODCOST = PRODCOST − (PRODCOST * 0.2) WHERE UPCASE(PRODTYPE) = 'LAPTOP'; SELECT * FROM PRODUCTS; QUIT;
PROC SQL; UPDATE PRODUCTS SET PRODCOST = PRODCOST - (PRODCOST * 0.2) WHERE UPCASE(PRODTYPE) = 'LAPTOP'; NOTE: 1 row was updated in WORK.PRODUCTS. SELECT * FROM PRODUCTS; QUIT; NOTE: PROCEDURE SQL used: real time 0.00 seconds |
Results
Product
Manufacturer Retail
Number Product Name
Number Product Type Product Cost
___________________________________________
________________________________
1110 Dream Machine 111
Workstation $3,200.00
1200 Business Machine 120
Workstation $3,300.00
1700 Travel Laptop 170
Laptop $2,720.00
2101 Analog Cell Phone 210
Phone $35.00
2102 Digital Cell Phone 210
Phone $175.00
2200 Office Phone 220
Phone $130.00
5001 Spreadsheet Software 500
Software $299.00
5002 Database Software 500
Software $399.00
5003 Wordprocessor Software 500
Software $299.00
5004 Graphics Software 500
Software $299.00 |