5_Sytax2_ModifyingData

SQL Syntax II: Modifying Data
Peter Komisar © Conestoga College version 1.1

reference: MySQL Reference Manual. MySQL Structured Query
Language(SQL) ,D.Geller, R. Roselius, Danielle Waleri, DDC
Publishing, Core MySQL, Leon Atkinson, Prentice-Hall Publishing,

Once data has been stored the job is over. The need
to modify or store other data soon is needed. More
data is added using INSERT statements. Existing
data may be modified using UPDATE statements.

The INSERT Statement

The INSERT statement has the following general form.

INSERT Statement FORM

INSERT INTO Table_Name [ column_list ] VALUES
( comma_separated_value_list | subquery )

Following is an example of an INSERT statement
for the store table we created.

Example

INSERT INTO store VALUES(1024, 2001, '101 Rally St.',
'Owen Sound', 'ON', 'L0Y2G1', '705', '6647782', 5.0, 8.0);

Just for Review the Steps needed to sign in and use
the correct database in MySQL are included below.

Execution

Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use bizzy;
Database changed
mysql> INSERT INTO store VALUES(1024, 2001, '101 Rally St.', 'Owen Sound', 'ON',
'L0Y2G1', '705', '6647782', 5.0, 8.0);
Query OK, 1 row affected (0.03 sec)

mysql> select street, city, province from store where postal_code='L0Y2G1';
+---------------+------------+----------+
| street        | city       | province |
+---------------+------------+----------+
| 101 Rally St. | Owen Sound | ON       |
+---------------+------------+----------+
2 rows in set (0.00 sec)

The Class Companion Manual suggests including the
column names in correct order explicitly in an INSERT
statement, makes for better maintenance. It becomes
a pretty long example in our case using the store table.

Example

mysql> INSERT INTO store(STORE_NUMBER,MANAGER_ID,STREET,
    -> CITY,PROVINCE,POSTAL_CODE,AREA_CODE,PHONE_NUMBER,
    -> GST_TAX_RATE,PST_TAX_RATE )
    -> VALUES
    -> (1024, 2001, '101 Rally St.', 'Owen Sound', 'ON',
    -> 'L0Y2G1', '705', '6647782', 5.0, 8.0);
Query OK, 1 row affected (0.03 sec)

Partial Inserts

If a few columns are selected and have values inserted,
the remaining fields in the row will take default values, if
they have been assigned or NULL.

In the following example, a store number is added to
a new row. The rest of the fields are assigned NULL.

Example

mysql> INSERT INTO store(STORE_NUMBER)
    -> values (9999);
Query OK, 1 row affected (0.01 sec)

The following SELECT statement shows some of
the fields that have been assigned NULL.

Example

mysql> select area_code,street, city, province
    -> from store where store_number = 9999;
+-----------+--------+------+----------+
| area_code | street | city | province |
+-----------+--------+------+----------+
| NULL      | NULL   | NULL | NULL     |
+-----------+--------+------+----------+
1 row in set (0.00 sec)

Using Nested Select Statements in Inserts

We first need to create another table which we
can populate with data from our store table.

Example

mysql> create table store_phoneNumber(
    -> store_number integer,
    -> full_phone_number char(11)
    -> );
Query OK, 0 rows affected (0.16 sec)

Now we can populate the new table from the
store table.

In the next example we show how a SELECT
statement can be nested as part of an INSERT.
Note the VALUES keyword is left off and replaced
by the SELECT in brackets.

Note that,the table created above intentionally represented
a merger of two pieces of phone data. This was done so
we could also provide another demonstration of how an SQL
function might be used in practical queries.

Example // nested SELECT, also a use of an SQL Function

INSERT INTO store_phoneNumber
(SELECT store_number, Concat(area_code,'-',phone_number)
   FROM store);

Execution

mysql> insert into store_phoneNumber
    -> (Select store_number, Concat(area_code,'-',phone_number)
    -> from store);
Query OK, 29 rows affected (0.03 sec)
Records: 29 Duplicates: 0 Warnings: 0

mysql> select * from store_phoneNumber;
+--------------+-------------------+
| store_number | full_phone_number |
+--------------+-------------------+
|         1001 | 204-3231122       |
|         1002 | 204-2039485       |
|         1003 | 780-3345555       |
|         1004 | 403-9192834       |
|         1005 | 306-1223344       |

// the middle of result set output is deleted for 'economy'

|         1024 | 705-6647782       |
|         1024 | 705-6647782       |
|         1024 | 705-6647782       |
|         1024 | 705-6647782       |
|         9999 | NULL            |
+-----------+---------------+
29 rows in set (0.00 sec)

The Update Statement

Often data will need to be modified. A customer's
address or phone number might be changed while
the rest of the information for the person remains
the same.

The Update Statement is used to modify table
data.

Typically, to update data you will need privileges to
change data in a table or you are the owner of the
table.

Following is the form of the Update command.

Form of the Update Command

UPDATE table_name SET column=expression
[,column=expression,...] [WHERE condition.]

Correcting the Store Table

When we created the store table we weren't too
concerned about the accuracy. The following table
provides information which we can use to make the
store table more correct.

PROVINCIAL GST/HST/PST Rates
Adapted from Government of Canada Website, Thanks to M.Cook!

Province	POSTAL SHORT FORM	GST/HST Rate (%)	PST Rate(%)	Combined Rate(%)
Alberta	AB	5	N.A.*	5
British Columbia	BC	5	7	12
Manitoba	MB	5	7	12
New Brunswick	NB	13	N.A.*	13
Newfoundland & Labrador	NL	13	N.A.*	13
Northwest Territories	NT	5	N.A.*	5
Nova Scotia	NS	13	N.A.*	13
Nunavut	NU	5	N.A.*	5
Ontario	ON	5	8	5
Prince Edward Island	PE	5	10	15.5**
Quebec	QC	5	7.5	12.875**
Saskatchewan	SK	5	5	10
Yukon Territory	YT	5	N.A.*	5

*Not Applicable ** some formulation

Our earlier store table has the wrong abbreviation for
Newfoundland. We have NF instead of NL. We can
correct that with the following update. In this case we
use the wrong value to target the appropriate rows.

Example

UPDATE store SET province ='NL' where province='NF';

The following SELECT show the Province abbreviation
has been corrected for the two Newfoundland Labrador
stores.

Execution

mysql> UPDATE store SET province ='NL' where province='NF';
Query OK, 2 rows affected (0.03 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> select city,province,postal_code
    -> from store where province='NL';
+---------------+----------+-------------+
| city          | province | postal_code |
+---------------+----------+-------------+
| St. John      | NL       | N3F1L3      |
| Labrador City | NL       | N7F8L2      |
+---------------+----------+-------------+
2 rows in set (0.00 sec)

The next SELECT confirms there are no rows with the
wrong abbreviation.

Execution

mysql> select city,province,postal_code
    -> from store where province='NF';
Empty set (0.00 sec)

Correcting the Tax Rate With UPDATES

The following SELECT shows the entries we have for
tax rate in the Newfoundland stores.

Execution

mysql> select GST_TAX_RATE, PST_TAX_RATE
    -> from store where province ='NL';
+--------------+--------------+
| GST_TAX_RATE | PST_TAX_RATE |
+--------------+--------------+
|          5.0 |          8.0 |
|          5.0 |          8.0 |
+--------------+--------------+
2 rows in set (0.02 sec)

But the Canadian Government table shown above shows
Newfoundland and Labrador use a combined HST tax
where both taxes are captured under the federal
tax and no tax is taken as a provincial tax.

For might change the GST amount to 13.0 percent and
the provincial rate to 0. In this case we will use the province
column to as the condition for where the tax changes will
be made.

Example

UPDATE store SET GST_TAX_RATE=13.0, PST_TAX_RATE=0.0
where province='NL';

'Problem Houston!'. We had set the GST range on the column
to only two decimal points. This is just as well as the combined
tax is not really GST, it is a HST tax. We will need to add a column
to our table. We cover altering tables in a later chapter. We
provide a preview of how to add a column to the end of the store
below.

Example That Previews of Adding a Column to the store Table

ALTER TABLE store ADD HST_TAX_RATE DECIMAL(3.1);

Execution

mysql> ALTER TABLE store ADD HST_TAX_RATE DECIMAL(3,1);
Query OK, 29 rows affected (0.30 sec)
Records: 29 Duplicates: 0 Warnings: 0

The following select shows the added column.

mysql> select GST_TAX_RATE,PST_TAX_RATE,HST_TAX_RATE
    -> from store where province='NL';
+--------------+--------------+--------------+
| GST_TAX_RATE | PST_TAX_RATE | HST_TAX_RATE |
+--------------+--------------+--------------+
|          5.0 |          8.0 |         NULL |
|          5.0 |          8.0 |         NULL |
+--------------+--------------+--------------+
2 rows in set (0.00 sec)

For Now

For now we don't want to mix topics too much so
for now let us just set both GST and PST taxes for
Newfoundland and Labrador to 0.0. ( If you can't
stand the implication, go ahead and add the HST
column and set it to 13.0 percent!).

Example

UPDATE store SET GST_TAX_RATE=0.0, PST_TAX_RATE=0.0
where province='NL';

Execution

mysql> UPDATE store SET GST_TAX_RATE=0.0, PST_TAX_RATE=0.0
    -> where province='NL';
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> select GST_TAX_RATE,PST_TAX_RATE
    -> from store where province='NL';
+--------------+--------------+
| GST_TAX_RATE | PST_TAX_RATE |
+--------------+--------------+
|          0.0 |          0.0 |
|          0.0 |          0.0 |
+--------------+--------------+
2 rows in set (0.00 sec)

The following makes the same change to stores in New Brunswick

Example

UPDATE store SET GST_TAX_RATE=0.0, PST_TAX_RATE=0.0
where province='NB';

We can use the IN function to change the PST to 7 in
BC and Manitoba.

Execution

mysql> UPDATE store SET GST_TAX_RATE=5.0, PST_TAX_RATE=7.0
    -> where province IN('BC','MB');
Query OK, 0 rows affected (0.00 sec)
Rows matched: 4 Changed: 0 Warnings: 0

mysql> select GST_TAX_RATE,PST_TAX_RATE
    -> from store where province IN('BC','MB');
+--------------+--------------+
| GST_TAX_RATE | PST_TAX_RATE |
+--------------+--------------+
|          5.0 |          7.0 |
|          5.0 |          7.0 |
|          5.0 |          7.0 |
|          5.0 |          7.0 |
+--------------+--------------+
4 rows in set (0.02 sec)

DELETE

The DELETE Command removes rows from tables.

First we will insert a store in the Yukon Territories.
Assume, that the same store after some time passed
had to be deleted as a better location was found.

In the following example we add the HST value.
For now we use the HST value to represent the
combined value of GST and PST.

Insert New Store

INSERT INTO store VALUES(1029, 2004, '3299 Main St.',
'Dawson City', 'YK', 'Y7K1U8', '403', '9241928', 5.0, 0.0, 5.0);

Execution

mysql> INSERT INTO store VALUES(1029, 2004, '3299 Main St.',
    -> 'Dawson City', 'YK', 'Y7K1U8', '403', '9241928', 5.0, 0.0,5.0);
Query OK, 1 row affected (0.02 sec)

mysql> Select city, province from store where store_number = 1029;
+-------------+----------+
| city        | province |
+-------------+----------+
| Dawson City | YK       |
+-------------+----------+
1 row in set (0.00 sec)

Deleting the Store

Suffice it say we need to be careful when using
delete. A delete without a where condition will
remove all the rows from a table!

Good Practice When Using Delete

It is a good practice to first select the row that needs
to be deleted. Once the desired row has been selected
the same where condition from the SELECT statement
can be used to remove the row.

The following removes the newly added row.

Example

DELETE FROM store WHERE store_number=1029;

Execution

mysql> DELETE FROM store WHERE store_number=1029;
Query OK, 1 row affected (0.03 sec)

Confirmation

mysql> Select city, province from store where store_number = 1029;
Empty set (0.00 sec)

A DELETE can be configured to select a group
of rows based on compound conditions. Again these
conditions can first be tested in a SELECT statement
to ensure they are correct before executing the
corresponding DELETE.

Concurrency

A major database may have many hundreds or
even thousands of clients being served at the
same time. Each client may be thought of as
a 'thread' of activity. Databases as such are
'multithreaded' environments.

To maintain data integrity, a locking mechanism
must be used to ensure only one client accesses
any one row at a time. The client's query must be
completed before another client is able to access
that same row.

The client has the 'lock' or in other words, the
row is locked from any other access until the
client has finished it's transaction.

Attempting to access a row on which there already
is a lock results in a wait state being entered or a
failure to access being reported.

Performance Effects of Locking

When one client's thread holds the lock on a resource
other threads must wait. This is also referred to as
'blocking'. When many threads block, the performance
of the database goes down.

To help reduce performance losses, it helps to lock
on more granular or smaller sections of data. This
reduces the competition between threads.

Lock Levels // scopes

Locks May be Applied at different levels of the
data system, at the row, page or table level.

Following is a description of different lock levels
and types of lock modes.

Lock Levels

row locks // individual rows
page locks // arbitrary sized blocks of data
table locks // locks all the rows of a table

Read & Write Lock Modes

Write Lock // aka Exclusive Lock

prevents other users from writing to a row.
Other users however can read the row.

Read Lock // aka Shared Lock maintains shared access

A shared or read lock allows other shared access
prevents a user from obtaining an exclusive lock

MySQL Locking

MySQL applies locks automatically without any
overt commands from clients. Single statements
are described as 'atomic'. This means that MySQL
ensures that a single statement will not be interrupted
and will complete fully before another thread works
on the same data.

Compound Single Statement Are Also Atomic

Knowing this, affords the opportunity to package
two statements together and have them have the
protection afforded a single statement.

Transactions

Often two or more statements cannot be reduced
to a single compound statement. Concurrency can
be applied to a group of statements using transactions.

Transaction are characterized by a defined beginning
and ending, the endings being a COMMIT statement
or a ROLLBACK.

Transaction are described as having the following
characteristics. The characteristics abbreviate to
ACID as we see below.

Transaction ACID Properties

atomicity
consistency
isolation
durability

Atomicity // the set of statements execute as a group

Again described, atomicity is the property of being
indivisible. With respect to a transaction, all the
statements execute or all abort. No intermediate
condition exists.

Consistency

Consistency is maintained by the database system
by serializing transactions or maintaining them in
order.

Isolation

The database system keeps transactions isolated
from each other. Changes made by a transaction are
available only after the transaction has 'committed' to
these changes.

Durability // permanent storage

Durability refers to the property where a transactions
changes are made permanently to the database.
This is a write to persistent storage memory that
can suffer a power loss.

How Transactions are Managed

The database system keeps a log of each statement
in a transaction. If a rollback occurs, The DBMS uses
the log to reverse the changes. Even if the DBMS
crashes it can return to a consistent state via the log.

The downside is that keeping such logs impact
performance.

MySQL MyISAM Tables

One of the reasons MySQL has a reputation for being
fast is that it's default table type, MyISAM does not
support transactions.

MySQL offers the option to support transactions. It
also supports extended table types such as InnoDB,
Berkeley DB and Gemini which support transactions.
MySQL is slower when supporting table types other
than it's default.

Isolation Levels

In theory, complete isolation of a transaction is best.
In practice this is very costly in terms of performance.
SQL describes four levels of isolation.

SQL Isolation Levels

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

These treat the problems listed below. We explain
both sets of terms below.

Transaction Related Problems

dirty reads
unrepeatable reads
phantom reads

READ UNCOMMITTED MODE
//read uncommitted changes

In READ UNCOMMITTED mode a 'dirty read'
is possible. This is one where a change has
been made on a row in a transaction however
the transaction hasn't been committed yet.

READ COMMITTED MODE
// limits the reads to committed values

READ COMMITTED mode attempts to deal
with the possibility of dirty reads by only reading
committed values. This creates the problem of
'unrepeatable reads'. In this case the transaction
queries a set of rows and reads them however
before it is finished, another transaction removes
those rows. The first transaction will get a different
result if it repeats the queries it had done.

REPEATABLE READ MODE
// locks applied, still let's phantom reads through

In REPEATABLE READ mode rows are locked
when a transaction reads or writes to them. This
may still lead to 'phantom' reads. While the query
has the lock ons on all affected rows, another
transaction may still insert new rows which will
change the results on a subsequent re-execution
of the same query.

SERIALIZABLE MODE

In SERIALIZABLE mode, transaction must be
executed one after each other. They are not
allowed to execute concurrently. This is the
behavior which is recommended as the
default by the SQL standard.

Anything less than the 'serializable' mode risks
corruption of the database integrity.

The following table summarizes the sorts of
reads possible with each sort of isolation level.

Reads Possible at Different Isolation Levels
// adapted from SQL Programming, D.Geller et.al

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read
Read Uncommitted	Yes	Yes	Yes
Read Committed	No	Yes	Yes
Repeatable Read	No	No	Yes
Serializable	No	No	No

Deadlock Resolution

If two transactions act on the same row simultaneously
resulting in a 'deadlock', (where neither transaction can
proceed) the database system takes charge and rolls
back one of the transactions.

AUTO-COMMIT is the Default

Auto-commit can be turned off with the SET
command. This however, effects all subsequent
statements. The other way is to use the BEGIN
command in conjunction with COMMIT or
ROLLBACK.

Note Transaction only apply to INSERT and
UPDATE statements. They do not apply to
changes to table structure.

Using Transactions in MySQL

MySQL is in AUTO-COMMIT mode by default.
Statements are committed immediately on
execution.

Transaction Example

The following example was reproduced from
an example in 'Core MySQL' by author, Leon
Atkinson.

mysql> create table config(name char(12),
    -> value integer);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into config values('seed', 12345);
Query OK, 1 row affected (0.02 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from config;
+------+-------+
| name | value |
+------+-------+
| seed | 12345 |
+------+-------+
1 row in set (0.00 sec)

mysql> select @seed:=Value
    -> from config
    -> where Name='seed';
+--------------+
| @seed:=Value |
+--------------+
|        12345 |
+--------------+
1 row in set (0.00 sec)

mysql> update config set value
    -> = RAND(@seed)*100000;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from config;
+------+-------+
| name | value |
+------+-------+
| seed | 66570 |
+------+-------+
1 row in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from config;
+------+-------+
| name | value |
+------+-------+
| seed | 12345 |
+------+-------+
1 row in set (0.00 sec)

MySQL Locking

Aside from MySQL's built in locking mechanism
explicit locks can be requested. The LOCK TABLE
statement will lock a whole table. The GET_LOCK
function can also be used.

MySQL allows locking rows, columns, pages or tables.
In MySQL a read lock allows other threads to read a
table as it is written to. A write lock disallows reads.

// read lock --> locked to shared readable state
// write lock--> locked to not shared readable

Where to Use A Table Lock

MySQL's table lock is very efficient and might be
suitable for web use. In the case where a database
has a lot of rollbacks row level locking may be a
better choice.

The UNLOCK TABLE statement unlocks a table.

GET_LOCK & RELEASE_LOCK Functions

Database systems generally may implement GET_LOCK
and RELEASE_LOCK functions which may be used
to obtain a lock at different levels of granularity and then
release it. The following examples shows how such
functions may be used.

MySQL's GET_LOCK Function

GET_LOCK(name, timeout)

// timeout is in seconds

In the following example, from 'Core MySQL', in the
first statement, a lock is obtained on the Price column
of the item table where ID is equal to 3.

Example // from 'Core MySQL' , by Leon Atkinson

SELECT GET_LOCK('Price on item.ID=3' , 60);
UPDATE item SET Price=3.15 WHERE ID=3;
SELECT RELEASE_LOCK('Price on item.ID=3');

Using lock methods is powerful as it provides the
integrity of transactions without the little of the
associated overhead in terms of performance loss.

Assignment

1) Update your provincial abbreviations and tax rates
so they are all correct in your table. Show the updates
used and the select statements that prove the changes
were made correctly.

If you already have everything correct in your store table
with respect to province abbreviations and taxes do the
following.

Enter three entries with faults, one for each of the Yukon,
North West Territories an Nunavik. Then use Updates to
correct the faults. Show all SQL statements, INSERTS,
SELECTS, and UPDATES and SELECTS.