Updating and Deleting Data - Learning MySQL and MariaDB - page 171

Because MySQL executes the subquery first, and separately from the UPDATE , there's no
problem with using the ORDER BY clause in it. Because the LIMIT clause is in an
UPDATE that's not using the multiple-table syntax, there's no problem using it either.
The preceding example may seem cumbersome, but it solves the problem. When you
can't do something the way you would think in MySQL, you can sometimes accomplish a
task with methods like using a subquery. Subqueries are coveredextensively in Chapter9 .
Handling Duplicates
In Chapter6 , we covered the INSERT statement indetail. We saw several variants on its
syntax and interesting ways to use it. This included INSERT...SELECT , a combination
of the INSERT and SELECT statements. There is anothercombination related to updating
rows, INSERT...ON DUPLICATE KEY UPDATE .
When inserting multiple rows of data, you may attempt inadvertently to insert rows that
would be duplicates: that is to say, rows with the same value that is supposed to be unique.
With the INSERT statement, you canadd the IGNORE flag to indicate that duplicate rows
should be ignored and not inserted. With the REPLACE statement, MySQL will replace
the existing rows with the new data, or rather it will delete the existing rows and insert the
new rows. As an alternative, you might want to keep the existing rows, but make a nota-
tion to them in each row. Such a situation is when INSERT...ON DUPLICATE KEY
UPDATE is useful. This will make more sense with an example.
Suppose there is another bird-watchers website similar to ours that's called Better Birders .
Because that site has become inactive and the owner wants to close it, he contacts us and
offers to redirect the site's traffic to our domain if we'll add its members to our member-
ship. We accept this offer, so he gives us a plain-text file with a list of each member's
name and email address. There are a few ways we might import those names; some are
covered in Chapter15 . But because some of the members of the other site may already be
members of our site, we don't want to import them and have duplicate entries. However,
we do want to make note of those people as being members of the other site in case we
want that information later. Let's try using INSERT...ON DUPLICATE KEY
UPDATE to do that. First we'll add a column to indicate that a member came from the
Better Birders site by using the ALTER TABLE statement like so:
ALTER TABLE humans
ADD COLUMN better_birders_site TINYINT DEFAULT 0 ;
This statement added a column named better_birders_site with a default value
of 0. If someone is a member of the Better Birders site, we'll set the column to 1. We'll