CC SQL 20- updating and deleting data

• The UPDATE statement is very easy to usesome would say too easy. The basic format of an UPDATE statement is made up of three parts:
• 1. The table to be updated
• 2. The column names and their new values
• 3. The filter condition that determines which rows should be updated.

Let's take a look at a simple example. Customer 10005 now has an email address, and so his record needs updating. The following statement performs this update:

• Input

• UPDATE customers
• SET cust_email = 'elmer@fudd.com'
• WHERE cust_id = 10005;

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;

When updating multiple columns, only a single SET command is used, and eachcolumn = value pair is separated by a comma. (No comma is specified after the last column.) In this example, columns cust_name and cust_email will both be updated for customer 10005.

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 ...

• The following statement deletes a single row from the customers table:
• • Input

• DELETE FROM customers
• WHERE cust_id = 10006;

Faster Deletes If you really do want to delete all rows from a table, don't use DELETE. Instead, use the trUNCATE TABLE statement that accomplished the same thing but does it much quicker (trUNCATEactually drops and recreates the table, instead of deleting each row individually).