SQL Update Statement

While the syntax and functionality seems trivial for easy cases UPDATE have and amazing amount of possibilities with subqueries.


   SET column_name = sql_expression
   WHERE search_condition

SET Syntax

   SET column_name = sql_expression

This clause assigns the constant value to the column identified by column_name. If sql_expression contains references to columns in the table being updated, the references are resolved in the context of the current row. The old column values are used on the right side of the equal sign.
   SET column_name = (subquery)

Assigns the value retrieved from the database by subquery to the column identified by column_name. The subquery must return exactly one row.
   SET (column_name, column_name, ...) = (subquery)

WHERE search_condition

Chooses which rows to update in the database table. Only rows that meet the search condition are updated. If you omit this clause, all rows in the table are updated.


   update cars 
   set sold = 1
   where inventory_id = 673412

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

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

This updates the very same table and the condition sets 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  <-----------+

Every row in the table to be updated (contract) that is matched in the sub-select (co.id) (all customers under 26 years) will be updated with 25% discount.

   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

Status: Published Date: 2017/02/14 22:10:39 Revision: 1.1

Copyright bei Andreas Haack (C) 2014. Diese Seite wird so wie sie ist zur Verfuegung gestellt, ohne irgenweche Garantien der Verwendbarkeit fuer bestimte Zwecke. Die auf dieser Seiten angebrachten Links liegen ausserhalb der redaktionellen Verantwortung von Andreas Haack und es wird keine Haftung oder Garantie uebernommen. Die Seiten sind Copyright (c) 2014 von Andreas Haack. Kein Teil darf ohne die schriftliche Einverstaendnis von Andreas Haack veroeffentlicht werden.
The page is provided 'as is' , without warranty of any kind, express or implied, including but not limited to the warranties of merchantability, fit- ness for a particular purpose and non-infringement. In no event shall Andreas Haack be liable for any claim, damages or other liability. This page is copyrighted property of Andreas Haack. Copyright by Andreas Haack (c) 2014 . No part of this page may be published without written permission for Andreas Haack. A hyper-link may created to this page but NOT to the embedded elements of this page. It may be freely downloaded for private purpose only as long as it is unaltered.