<<

. 15
( 132 .)



>>


TABLE 3-5 RESULTS OF THE UPDATE

id fname lname Income folks_salary income_salary

Don Ho 500,000 50,000 50,000
1

Vito Corleone 1,500,000 968,000 1,064,800
2

Don Juan 250 50,000 50,000
3

Don Johnson 1,250,000 60,500 66,550
4




What™s happening is that in the first part of the set clause, folks.salary =
folks.salary * 1.1, the salary field is being set to its current value times 1.1;
but in the second part of the set clause, income.salary = folks.salary * 1.1,
the new value of folks.salary is being used. Thus, income.salary ends up being
set to the original value of folks.salary times 1.21 (1.1 twice).
Plus, for even more fun, if we switch the order in which the tables to be updated
are listed, as in the following code, we see “reasonable” results again (Table 3-6).
Chapter 3: Inserting, Editing, and Selecting Data 59

update income, folks set
income.salary = folks.salary * 1.1
, folks.salary = folks.salary * 1.1
where folks.id = income.id and income.income >= 1000000
;



TABLE 3-6 RESULTS OF THE UPDATE

id fname lname income folks_salary income_salary

Don Ho 500,000 50,000 50,000
1

Vito Corleone 1,500,000 968,000 968,000
2

Don Juan 250 50,000 50,000
3

Don Johnson 1,250,000 60,500 60,500
4




The tables are updated in the order in which they are listed, and the query runs
as if it were actually two updates in order:

update income, folks set
income.salary = folks.salary * 1.1
where folks.id = income.id and income.income >= 1000000
;
update income, folks set
folks.salary = folks.salary * 1.1
where folks.id = income.id and income.income >= 1000000
;

When you look at it as two queries, the results make sense. We recommend that
you stick to updating a single table at a time for the sake of clarity if nothing else,
unless you have a good reason to do otherwise.
Note that this syntax is not standard ANSI SQL syntax. This matters primarily
for the portablility of your application; it™s a good reason to isolate the code that
actually performs updates.



The delete Statement
The delete statement removes a row or multiple rows from a table. The syntax is
as follows:

delete from table_where where-clause
60 Part I: Working with MySQL

To remove Don Ho from Table 3-1, you™d run the following statement:

delete from folks where id=1;

You can delete records from one or more tables at a time, based on the data in
those tables as well as others (this capability is as of MySQL 4.0):

delete from table1 [, table2 [, ...]] using table1 [, table2 [,
...]] [, additional_table_1 [, additional_table2 [,...]]] where
where-clause



This is just one of a few supported formats for a multi-table delete state-
ment. We™re using it because it is most similar to the single-table delete,
which means we™re a smidge less likely to get the syntax wrong.



The tables listed in the from clause are the ones from which records are deleted.
Those same tables appear again in the using clause, along with any other tables
you wish to query to determine what records you want to delete.
To illustrate, we can remove the underachievers from the folks table. Tables 3-7
and 3-8 provide the data used in the example again.


TABLE 3-7 THE FOLKS TABLE

id Fname Lname Salary

Don Ho 25,000
1

Don Corleone 800,000
2

Don Juan 32,000
3

Don Johnson 44,500
4




TABLE 3-8 THE INCOME TABLE

id Income

500,000
1

1,500,000
2
Chapter 3: Inserting, Editing, and Selecting Data 61



id Income

250
3

1,250,000
4




Now we can use the delete statement to remove records from the folks table
for people whose income is too low, as demonstrated in the following code. Table
3-9 displays the results.

delete from folks using folks, income
where folks.id = income.id and income.income < 100000
;



TABLE 3-9 THE FOLKS TABLE

id Fname lname Salary

Don Ho 25,000
1

Don Corleone 800,000
2

Don Johnson 44,500
4




The replace Statement
You won™t find MySQL™s replace statement in other database systems, and it is not
part of the SQL standard. However, it is convenient in places. The replace state-
ment works with a row for which you know what the primary key should be. When
you run the replace statement, MySQL searches for a row with the primary key
indicated in the statement. If a row with the indicated primary key exists, that row
is updated. If not, a new row is inserted. The basic syntax is as follows:

Replace into table_name (col_1, col_2, ?) values (val_1, val_2, ?)

For an example of a situation in which replace would be helpful, imagine you
have a table with two columns, email and full_name, with email as the primary
key. If you want to write a script that gives a user the opportunity to insert and edit
this information, you would have some sort of form with which the user could enter
62 Part I: Working with MySQL

the data. Then, when the user submits the form, the script would have to go through
some decision logic. Without replace, the logic would be something like this:

examine form data
delete record from database with the submitted primary key value
(this will run with no results if no such record exists)
run insert statement

But because MySQL has the replace statement, you can lose all of this logic and
just run replace. For example:

replace into users (email, full_name) values (˜jon@doe.com™, ˜Jon
Doe™)

Note that you don™t need to use a where clause to identify the row that you are
replacing; MySQL handles this, based on the value of the primary key. (If you use
the replace statement on a table with no defined primary key, MySQL inserts a
new record into the table.)
However, you can use a where clause to identify the source of the new data, and
that can come in very handy. Suppose you want to change the values of a field in
one table to reflect the result of an aggregate query against another table. You can™t
do this with an update statement because group by clauses are not allowed there.
But replace accepts a select statement as its source (just like insert). If the table
you are updating has a unique key (see why they™re so handy?), you™re in gravy.
To illustrate, we add a third table to the set of example tables. Table 3-10 records
donations brought in by each of the fellows:


TABLE 3-10 THE DONATIONS TABLE

Id amount date

5000 3/1/2003
1

5000 3/2/2003
1

5000 3/3/2003
1

25000 3/1/2003
2

3000 3/2/2003
2

4000 3/2/2003
2

10000 3/3/2003
2

1000 3/1/2003
3
Chapter 3: Inserting, Editing, and Selecting Data 63



Id amount date

3.15 3/2/2003
3

25 3/3/2003
3

10000 3/1/2003
4

20000 3/2/2003
4



<<

. 15
( 132 .)



>>