SQL Update Statement

Syntax

   UPDATE TABLE_NAME [ALIAS]
   SET column_name = sql_expression
   WHERE search_condition

• UPDATE has three parts
• The UPDATE table_name defines the table that is updated
• The SET part gives a list of columns that can be set to constants or (complex) subqueries
• The WHERE condition which defines which rows are updated.

SET Syntax

   SET column_name = sql_expression

   SET column_name = (subquery)

   SET (column_name, column_name, ...) = (subquery)

WHERE search_condition

Samples

• Trivial case typical of an OLTP system where one row is updated

   update cars 
   set sold = 1
   where inventory_id = 673412

   update cars 
   set sold = 1, udate = sys date
   where inventory_id = 673412

• Trivial case of multiple update

   update contract
   set discount = 25 -- %
   where contract_type = 'Student'

• Complex case with sub-select determines which rows are updated

  update contract co              -------------+
  set discount = 25 -- %                       |
  where                                        |
  exists (select 1 from  customer cu           |
          where age < 26                       |
                and cu.id = co.id  <-----------+
         );

• Complex case with sub-select determines which rows are updated

   update contract co             -------------+
   set discount =                              | 
   (select                                     |
       case                                    |
       when age <= 18 then 25                  |
       when age >= 65 then 14                  |
       else 5                                  |
       end as age                              |
       from  customer cu                       | 
       where cu.id = co.id  <------------------+
   )                                           |
  where                                        |
  exists (select 1 from  customer cu           |
          where age <= 26 or age >= 65         |
                and cu.id = co.id  <-----------+
         );

Where clause

All contracts if customers that are 26 or younger or 65 or older will be updated

Al other customers keep there discount unchanged

Set clause

Customers of age 18 and younger get 25% discount Customers of age 65 and older get 14% discount

All other get 5% => The are customers between 18 and 26 years