VOICE Home Page: http://www.os2voice.org
Newsletter Index
< Previous Page | Next Page >
Feature Index
|
VOICE Home Page: http://www.os2voice.org |
June 2004
Newsletter Index
|
| By Wolfgang Draxler © June 2004, Translation: John Armstrong |
Today we take a closer look at the SQL commands "update" and "delete."
But before we explore those two commands, there is a small additional feature of the SQL command "select" to discuss. If you take another look at the discussion in the previous installment of this series you see that I did not mention the "order by" clause of "select." I do so now.
The "order by" clause sorts the data according to the given field. Moreover, the sort order on the given field can be specified as "asc" (ascending) or "desc" (descending). If you don't specify "asc" or "desc," the default is "asc," i.e. ascending.
First we will invoke the command without the "order by" clause.
mysql> select * from country; +-------------+-------------------------+---------+ | countrycode | countryname | prefix | +-------------+-------------------------+---------+ | A | Austria | +43 | | D | Germany | +49 | | CH | Switzerland | +41 | | GB | Great Britain | +44 | | USA | United States of America| +1 | | I | Italy | +39 | | H | Hungary | +36 | | NL | Netherlands | NULL | | L | Luxemburg | NULL | | B | Belgium | NULL | +-------------+-------------------------+---------+ 10 rows in set (0.07 sec)
And now with the first field specified in the "order by" clause:
mysql> select * from country order by countrycode; +-----------+-------------------------+---------+ |countrycode| countryname | prefix | +-----------+-------------------------+---------+ | A | Austria | +43 | | B | Belgium | NULL | | CH | Switzerland | +41 | | D | Germany | +49 | | GB | Great Britain | +44 | | H | Hungary | +36 | | I | Italy | +39 | | L | Luxemburg | NULL | | NL | Netherlands | NULL | | USA | UnitedStates of America | +1 | +-----------+-------------------------+---------+ 10 rows in set (0.06 sec)
As you can see, the data entries are now sorted according to the abbreviated country name. Naturally one could also give the command in the following form:
mysql> select * from country order by countrycode asc;
If you want the reverse order, the complete command is:
mysql> select * from country order by countrycode desc; +-------------+-------------------------+---------+ |countrycode | countryname | prefix | +-------------+-------------------------+---------+ | USA | United Statesof America | +1 | | NL | Netherlands | NULL | | L | Luxemburg | NULL | | I | Italy | +39 | | H | Hungary | +36 | | GB | Great Britain | +44 | | D | Germany | +49 | | CH | Switzerland | +41 | | B | Belgium | NULL | | A | Austria | +43 | +-------------+-------------------------+---------+ 10 rows in set (0.00 sec)
As already discussed one can specify several fields and give the sort order for each field separately. For example:
mysql> select * from country order by countryname asc, countrycode desc; +-------------+-------------------------+---------+ | countrycode | countryname | prefix | +-------------+-------------------------+---------+ | B | Belgium | NULL | | D | Germany | +49 | | GB | Great Britain | +44 | | I | Italy | +39 | | L | Luxemburg | NULL | | NL | Netherlands | NULL | | A | Austria | +43 | | CH | Switzerland | +41 | | H | Hungary | +36 | | USA | UnitedStates of America | +1 | +-------------+-------------------------+---------+ 10 rows in set (0.00 sec)
(That command is not very pretty but I couldn't think of a meaningful combination. :-) )
For the "lazy typist" one can give the column number instead of the field-name in which case the command looks like:
mysql> select * from country order by 2 asc, 1 desc; +-------------+-------------------------+---------+ | countrycode | countryname | prefix | +-------------+-------------------------+---------+ | B | Belgium | NULL | | D | Germany | +49 | | GB | Great Britain | +44 | | I | Italy | +39 | | L | Luxemburg | NULL | | NL | Netherlands | NULL | | A | Austria | +43 | | CH | Switzerland | +41 | | H | Hungary | +36 | | USA | UnitedStates of America | +1 | +-------------+-------------------------+---------+ 10 rows in set (0.01 sec)
Not very pretty, either. Moreover, it is not very readable because of the wild card placeholder. If there are subsequent changes in the structure of the table, sorting errors can creep in. For that reason one should always give the field names. In the present case that would be:
mysql> select countrycode, countryname, prefix from country order by 2 asc, 1 desc;
Naturally the "where" condition can be combined with the "order by" clause, e.g.
mysql> select * from country where prefix is null order by countrycode; +-------------+-------------+--------+ | countrycode | countryname | prefix | +-------------+-------------+--------+ | B | Belgium | NULL | | L | Luxemburg | NULL | | NL | Netherlands | NULL | +-------------+-------------+--------+ 3 rows in set (0.01 sec)
MySQL, as well as other SQL servers, first process the conditions and only at the end do they sort the result according to the "order by" clause.
I have not yet exhaustively explored the "select" command. We have not dealt with the "group by" clause, the encapsulation of tables, and joining of different tables. That is because with the single table and data we have been using, there are no good examples of grouping. If requested, I will expand on these topics at a later time.
Now we examine the two other commands.
The syntax for the "update" command is:
update <table> set <field> = <value>, [<field> = <value>, ...] [where <condition>]
This seems more complicated than it really is. One specifies the table to be updated, and which fields are to be changed (the "set" section.) The "where" condition determines which records will be affected. If this condition is not specified, all records will be changed. Hence one must always be careful to provide a meaningful "where" condition. Otherwise, the results can be very different from what one expects, and take a lot of time to correct. [Ed. note: MySQL is not transaction based. Any changes made to a table are committed immediately. Other database managers require an explicit "commit" command to make changes permanent, or can undo all the uncommited changes with "rollback."]
Let's try an example:
mysql> update country set prefix="+32" where countrycode="B"; Query OK, 1 row affected (0.01 sec) Records found: 1 Changes: 1 Warnings: 0
This command doesn't do anything except change the prefix of Belgium from NULL to +32. To check we execute the following select-command:
mysql> select * from country where countrycode = "B"; +-------------+-------------+---------+ | countrycode | countryname | prefix | +-------------+-------------+---------+ | B | Belgium | +32 | +-------------+-------------+---------+ 1 row in set (0.00 sec)
As you see, it worked.
The server proceeds as follows:
Determine the required tables
Execute the where-condition and produce an internal results-list
Read in the records from this list
Change the indicated fields
To exhibit this procedure more clearly consider the following example:
mysql> select * from country where prefix is null; +-------------+-------------+--------+ | countrycode | countryname | prefix | +-------------+-------------+--------+ | NL | Netherlands | NULL | | L | Luxemburg | NULL | +-------------+-------------+--------+
As you see, the Netherlands and Luxemburg have no prefix. Now, for these two countries we change the text to "N.A." (Not Available). To do this we use the "where" condition of the "select" command ("where prefix is null") and include this in the "update" command. This produces the following command:
mysql> update country
-> set prefix = 'N.A.'
-> where prefix is null;
Query OK, 2 rows affected (0.01 sec)
Records found: 2 Changed: 2 Warnings: 0
As a check we again issue the "select" command:
mysql> select * from country where prefix is null; Empty set (0.03 sec)
That result -- or better said, that absence of result -- is now correct because there is now no country whose prefix has no value (i.e. is null). To underline this execute the following command:
mysql> select * from country; +-------------+-------------------------+---------+ | countrycode | countryname | prefix | +-------------+-------------------------+---------+ | A | Austria | +43 | | D | Germany | +49 | | CH | Switzerland | +41 | | GB | Great Britain | +44 | | USA | UnitedStates of America | +1 | | I | Italy | +39 | | H | Hungary | +36 | | NL | Netherlands | N.A. | | L | Luxemburg | N.A. | | B | Belgium | +32 | +-------------+-------------------------+---------+ 10 rows in set (0.00 sec)
As you see, each country now has a prefix value, and on closer inspection you see that the Netherlands and Luxemburg have the value "N.A."
Now we provide these two countries with the correct value of the prefix, namely:
mysql> update country
-> set prefix="+352"
-> where countrycode="L";
Query OK, 1 row affected (0.02 sec)
Records found: 1 Changed: 1 Warnings: 0
mysql> update country
-> set prefix="+31"
-> where countrycode"NL";
Query OK, 1 row affected (0.02 sec)
Records found: 1 Changed: 1 Warnings: 0
Now we find:
mysql> select * from country; +-------------+-------------------------+---------+ | countrycode | countryname | prefix | +-------------+-------------------------+---------+ | A | Austria | +43 | | D | Germany | +49 | | CH | Switzerland | +41 | | GB | Great Britain | +44 | | USA | United States of America| +1 | | I | Italy | +39 | | H | Hungary | +36 | | NL | Netherlands | +31 | | L | Luxemburg | +352 | | B | Belgium | +32 | +-------------+-------------------------+---------+ 10 rows in set (0.00 sec)
One may also change several fields at once in an "update" command. (Depending, naturally, on the records found.) This is done in the "set" section of the command and the fields to be altered are separated by commas.
As an example:
mysql> update country
-> set countrycode="Oe", countryname="OESTERREICH", prefix="N.A."
-> where countrycode="A";
Query OK, 1 row affected (0.02 sec)
Records found: 1 Changed: 1 Warnings: 0
The result is:
mysql> select * from country; +-------------+-------------------------+---------+ | countrycode | countryname | prefix | +-------------+-------------------------+---------+ | Oe | OESTERREICH | N.A. | | D | Germany | +49 | | CH | Switzerland | +41 | | GB | Great Britain | +44 | | USA | UnitedStates of America | +1 | | I | Italy | +39 | | H | Hungary | +36 | | NL | Netherlands | +31 | | L | Luxemburg | +352 | | B | Belgium | +32 | +-------------+-------------------------+---------+ 10 rows in set (0.00 sec)
To put things as they were we execute:
mysql> update country
-> set countrycode="A", countryname ="Austria", prefix="+43"
-> where countrycode = "Oe";
Query OK, 1 row affected (0.00 sec)
Records found: 1 Changed: 1 Warnings: 0
mysql> select * from country; +------- --+-------------------------+---------+ | countrycode| countryname | prefix | +------------+-------------------------+---------+ | A | Austria | +43 | | D | Germany | +49 | | CH | Switzerland | +41 | | GB | Great Britain | +44 | | USA | UnitedStates of America | +1 | | I | Italy | +39 | | H | Hungary | +36 | | NL | Netherlands | +31 | | L | Luxemburg | +352 | | B | Belgium | +32 | +------------+-------------------------+---------+ 10 rows in set (0.00 sec)
Thank goodness! All is right with the world again. :-)
We still have the "delete" command to explore.
This command erases records from a table. The syntax is relatively simply:
delete from <Table> [where <condition>]
One can remove a group of records, or all the records. As with the select- and update-commands, this depends on the where-condition. If one omits the where-condition, MySQL erases all records from the table. Hence one must pay close attention (like the update-command) to how one uses the where-condition.
As an example, we remove the record for Austria (and then reinstate it).
mysql> delete from country where countrycode='A'; Query OK, 1 row affected (0.03 sec)
Let's see if the country was really removed.
mysql> select * from country; +-------------+-------------------------+---------+ | countrycode | countryname | prefix | +-------------+-------------------------+---------+ | D | Germany | +49 | | CH | Switzerland | +41 | | GB | Great Britain | +44 | | USA | UnitedStates of America | +1 | | I | Italy | +39 | | H | Hungary | +36 | | NL | Netherlands | +31 | | L | Luxemburg | +352 | | B | Belgium | +32 | +-------------+-------------------------+---------+ 9 rows in set (0.00 sec)
You see that the country really has been removed and the number of records has decreased by 1.
Now we reinstate the record.
mysql> insert into country values ('A', 'Austria', '+43');
Query OK, 1 row affected (0.02 sec)
Now all the records are again present in the table.
This ends the present article; in the next we deal with a small REXX program that reads this table.
References:
|
Feature Index
editor@os2voice.org
< Previous Page | Newsletter Index | Next Page >
VOICE Home Page: http://www.os2voice.org