6.4. Updating Data in a Table

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;

SAS Log Results

      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


..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.17.79.206