MariaDB Crash Course - Ben Forta [60]
Updating multiple columns requires a slightly different syntax:
Input
UPDATE customers
SET cust_name = 'The Fudds',
cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;
Analysis
When updating multiple columns, only a single SET command is used, and each column = value pair is separated by a comma. (No comma is specified after the last column.) In this example, columns cust_name and cust_email are updated for customer 10005.
* * *
Tip: Using Subqueries in an UPDATE Statement
Subqueries may be used in UPDATE statements, enabling you to update columns with data retrieved with a SELECT statement. Refer to Chapter 14, “Working with Subqueries,” for more information on subqueries and their uses.
* * *
* * *
Tip: The IGNORE Keyword
If your UPDATE statement updates multiple rows and an error occurs while updating one or more of those rows, the entire UPDATE operation is cancelled (and any rows updated before the error occurred are restored to their original values). To continue processing updates, even if an error occurs, use the IGNORE keyword, like this:
UPDATE IGNORE customers ...
* * *
To delete a column’s value, you can set it to NULL (assuming the table is defined to allow NULL values). You can do this as follows:
Input
UPDATE customers
SET cust_email = NULL
WHERE cust_id = 10005;
Here the NULL keyword is used to save no value to the cust_email column.
Deleting Data
To delete (remove) data from a table, the DELETE statement is used. DELETE can be used in two ways:
• To delete specific rows from a table
• To delete all rows from a table
We now take a look at each of these.
* * *
Caution: Don’t Omit the WHERE Clause
Special care must be exercised when using DELETE because it is all too easy to mistakenly delete every row from your table. Please read this entire section on DELETE before using this statement.
* * *
* * *
Tip: DELETE and Security
Use of the DELETE statement can be restricted and controlled. More on this in Chapter 28.
* * *
I already stated that UPDATE is easy to use. The good (and bad) news is that DELETE is even easier to use.
The following statement deletes a single row from the customers table:
Input
DELETE FROM customers
WHERE cust_id = 10006;
Analysis
This statement should be self-explanatory. DELETE FROM requires that you specify the name of the table from which the data is to be deleted. The WHERE clause filters which rows are to be deleted. In this example, only customer 10006 will be deleted. If the WHERE clause were omitted, this statement would delete every customer in the table.
DELETE takes no column names or wildcard characters. DELETE deletes entire rows, not columns. To delete specific columns use an UPDATE statement (as seen earlier in this chapter).
* * *
Note: Table Contents, Not Tables
The DELETE statement deletes rows from tables, even all rows from tables. But DELETE never deletes the table itself.
* * *
* * *
Tip: Faster Deletes
If you really do want to delete all rows from a table, don’t use DELETE. Instead, use the TRUNCATE TABLE statement, which accomplishes the same thing but does it much more quickly (TRUNCATE actually drops and re-creates the table, instead of deleting each row individually).
* * *
Guidelines for Updating and Deleting Data
The UPDATE and DELETE statements used in the previous sections all have WHERE clauses, and there is a good reason for this. If you omit the WHERE clause, the UPDATE or DELETE is applied to every row in the table. In other words, if you execute an UPDATE without a WHERE clause, every row in the table is updated with the new values. Similarly if you execute DELETE without a WHERE clause, all the contents of the table are deleted.
Here are some best practices that many SQL programmers follow:
• Never execute an UPDATE or a DELETE without