|=--------------------------------------------------=|
  _________________  .____     ___________________
 /   _____/\_____  \ |    |    \_   _____/\______ \
 \_____  \  /  / \  \|    |     |    __)_  |    |  \
 /        \/   \_/.  \    |___  |        \ |    `   \
/_______  /\_____\ \_/_______ \/_______  //_______  /
        \/        \__>       \/        \/         \/


|=--------------------------------------------------=|
. |03.09 - Updating records                          .
|=--------------------------------------------------=|

  Once the records are  inserted into the table there
could come need  to modify it. For  this purposes SQL
offers UPDATE type of query.

  We can  modify rows using the  update. Let's assume
the user wrote  his note into the NOTES  table and he
would like to update it.

  Then following query can be used:

  update notes
  set no_text = 'Updated'
  where no_key = 3; 

  That's give us following result:

  select n.*
  from notes n
  where no_key = 3;

  Like this:

    no_key  no_text      user_key
  --------  ---------  ----------
         3  Updated             3

  Let's assume we  would like to clear  all notes for
given user.  So we will use  subquery lookup user_key
and then update all the notes for given user. Examine
following query:

  update notes
  set no_text = null
  and no_text is not null
  where no_key in (
    select no_key from notes
    where user_key in (
      select user_key from users
      where user_name = 'user3'
    )
  );

  The above query could be run multiple time but only
once it  would update  some data.  See the  part with
no_text is  not null.  That would prevent  updates on
the  next run  despite all  the given  rows would  be
already empty (or null).

  Result of the table notes  after this part would be
following:

  select
    n.user_key,
    n.no_key,
    n.no_text
  from notes n
  order by user_key, no_key;

  With following result:

  user_key    no_key  no_text
  ----------  --------  ------------
           1         1  Hello World!
           1         4  Notes no. 2
           1         7  Notes no. 5
           1        10  Notes no. 8
           2         5  Notes no. 3
           2         8  Notes no. 6
           3         3 
           3         6  
           3         9  

  And that's it for this part.


|=--------------------------------------------------=|
; v o | Thanks Linux and Vim | Visit SQLOK | Donate  ;
|=--------------------------------------------------=|