_________________ .____ __________________

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

|=--------------------------------------------------=|
. |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.

|=--------------------------------------------------=|
|=--------------------------------------------------=|