SELECT * FROM Customers WHERE Customer_ID = 12
This statement should only return one row of data containing the information you want to update.
Because it only returned one row of data, you can be sure that only one row will be affected when you
execute your UPDATE statement. This extra step can save you a lot of grief in the long run.
MySQL has also added an extra feature to the UPDATE statement. If you want to execute an UPDATE
statement when no one else is using the table you are updating you can set the priority to low. This
causes the UPDATE statement to execute only when the table is not being used. The syntax looks like
this:
UPDATE LOW_PRIORITY Customers SET First_Name = "Judy"
WHERE Customer_ID =16
MySQL is also smart enough to know if you are trying to update a value with the value that the field
already contains. If you were trying to update a customer's record that originally had Steve in the
First_Name column with the value Steve, MySQL would be smart enough to recognize this and
ignore the operation. This saves on CPU cycles and helps MySQL be more efficient overall.With MySQL, you also can update a value based on the value it currently holds. For example, if you
wanted to add one year to everyone's age in the database, you could use the following statement:
UPDATE Customers SET Age = Age + 1
It is important to remember that MySQL evaluates UPDATE statements from left to right.
Another safeguard you can implement when updating the database is to use the LIMIT function Using
this will control the number of rows that are affected by your UPDATE statement. If you wanted to limit
the number of rows you updated in the last example, you could do the following:
UPDATE Customers SET Age = Age + 1 LIMIT 10This statement would limit the update to the first 10 rows of the table. That way, you can check your
work and make sure that your SQL statement did exactly what you wanted it to do.DELETE
The DELETE statement is very similar to the SELECT statement. The only difference is that instead of
selecting records to view, this statement deletes those records. Again, caution should be used when issuing
this statement to avoid accidentally deleting rows in error. MySQL does not provide any warnings when you
are deleting records. So be very careful. You can exercise the same precautions described previously—
performing a SELECT statement first to ensure that you are deleting the records you want to delete and using
the LIMIT keyword to ensure only the number of rows you specify are deleted.
The DELETE syntax looks like the following:
DELETE FROM Customers WHERE State = "KY"
Like all SQL statements, the verb comes first. Following the verb is the keyword FROM, followed by the
name of the table from which you want to delete the records. Following the table name is the WHERE
clause. The WHERE clause is very important in a DELETE statement. This limits the number of deletions
based on the criteria that are set here. Without the WHERE clause, all records from the database are
deleted by default. The following syntax would delete everything in the Customers table:
DELETE FROM Customers
Like all the previous statements, MySQL provides a priority level for the query. To have a DELETE
statement execute while no one else is reading from the table from which you want to delete records,
you could issue the following statement:
DELETE LOW_PRIORITY FROM Customer WHERE State = "KY"
A DELETE statement on a very large table can take some time. To free up the table for other queries,
you can use multiple LIMIT statements. You can set the limit to 10 and execute the query multiple
times. Each time, it will delete at most 10 rows. When the WHERE clause is no longer true, it will not
return any rows. For example, your client wants you to delete all the customers from Kentucky because
his or her store no longer supports that state. Perform your initial SELECT query:
SELECT * FROM Customers WHERE State = "KY"
This query returns 2756 records from your database. You now want to take advantage of the LIMIT key
word to optimize the performance of your table:
DELETE FROM Customers WHERE State = "KY" LIMIT 100
This query would delete up to 100 rows of data that match the criteria in your WHERE clause. So, 2656
records would be left that matched your WHERE criteria. You would then perform the query again, and
100 more records would be deleted, leaving you with 2556. Every time this query is run, the number of