MySQL must know-20th Chapter-Update and delete data

20th-Updating and deleting data

This chapter describes how to use Update and DELETE statements to further manipulate table data.

20.1 Updating data

In order to update (modify) The data in the table, you can use the UPDATE statement. Update can be used in two ways;

• Update a specific row in a table;
• Update all rows in the table.
  Each of them is described below.
  Do not omit the WHERE clause when using UPDATE, be careful. Because you are not careful, all rows in the table are updated. Please read this section thoroughly before using this statement.
  Update and security can restrict and control the use of the UPDATE statement, see Chapter 28th for more information.

The UPDATE statement is very easy to use, even too easy to use. The basic UPDATE statement consists of 3 parts, respectively;

• the table to update;
• Column names and their new values; 140 updating and deleting data
• Determines the filter criteria to update rows.

Give a simple example. Customer 10005 now has an e-mail address, so his record needs to be updated with the following statement

The UPDATE statement always starts with the name of the table that you want to update. In this example, the name of the table to be updated is customers. The set command is used to assign the new value to the column being updated. As shown here, the SET clause sets the value specified by the Cust_email column.
: Set cust_email= ' [email protected] @com '

The UPDATE statement ends with a WHERE clause that tells MySQL which row to update. Without the WHERE clause, MySQL will update all the rows in the Customers table with this email address, which is not what we want.
The syntax for updating multiple columns is slightly different;

When updating multiple columns, you only need to use a single set command, with each column = value pair separated by commas (after the last column without a comma). In this example, the Cust_name and Cust_email columns of customer 10005 are updated.

The subquery can be used in the UPDATE statement using a subquery in the UPDATE statement, allowing the column data to be updated with the data retrieved from the SELECT statement. For more information on subqueries and their use, see Chapter 14th.

Ignore keywords If you update multiple rows with an UPDATE statement and an existing error occurs when one or more rows are updated, the entire update operation is canceled (all rows that were updated before the error occurred are restored to their original values). To continue the update even if an error occurs, use the Ignore keyword, as shown below; UPDATE IGNORE Customers ...
To remove a column's value, you can set it to null if the table definition allows null values. proceed as follows;

Where null is used to remove the value from the Cust_email column.

20.2 Deleting data

To delete (remove) data from a table, use the DELETE statement. Delete can be used in two ways;

• Deletes a specific row from the table;
• Removes all rows from the table.
  Each of them is described below.
  Do not omit the WHERE clause when using delete must be careful. Because you are not careful, you will mistakenly delete all rows in the table. Please read this section thoroughly before using this statement. Delete and security can restrict and control the use of DELETE statements, see Chapter 28th for more information. As I said earlier, update is very easy to use, and delete is easier to use.
  The following statement removes a row from the Customers table;
  

This statement is easy to understand. Delete from requires that you specify the name of the table from which to delete data. The WHERE clause filters the rows to be deleted. In this example, only customer 10006 is deleted. If you omit the WHERE clause, it deletes each customer in the table. Delete does not require a column name or wildcard character. Delete Deletes the entire row instead of deleting the column. In order to delete the specified column, use the UPDATE statement.

Delete the contents of the table instead of the table DELETE statement to delete rows from the table, or even delete all rows in the table. However, delete does not delete the table itself.

Faster deletion do not use Delete if you want to remove all rows from the table. You can use the TRUNCATE TABLE statement, which accomplishes the same work, but is faster (truncate actually deletes the original table and re-creates a table instead of deleting the data in the table row by line).

20.3 guidelines for updates and deletions

The update and DELETE statements used in the previous section all have a WHERE clause, which is a good reason to do so. If the WHERE clause is omitted, the update or delete is applied to all rows in the table. In other words, if you perform an update without a WHERE clause, each row in the table will be updated with the new value. Similarly, if you execute a DELETE statement without a WHERE clause, all data for the table is deleted. Here are the habits that many SQL programmers follow when using update or delete.

• Never use an UPDATE or DELETE statement without a WHERE clause unless you are sure you want to update and delete each row.
• Ensure that each table has a primary key (if you forget this, see Chapter 15th), use it as much as you like WHERE clause (you can specify each primary key, multiple values, or a range of values).
• Before using the WHERE clause on an UPDATE or DELETE statement, you should test with select to ensure that it filters the correct records in case the WHERE clause you are writing is incorrect.
• Use a database that enforces referential integrity (see Chapter 15th for this content) so that MySQL will not allow the deletion of rows with data associated with other tables.

Be careful with MySQL without the undo button. You should use update and delete very carefully, or you may find yourself updating or deleting the wrong data. 19020.4 Summary 143

20.4 Summary

In this chapter, we learned how to use Update and DELETE statements to work with data in tables. We learned the syntax of these statements and knew their inherent dangers. This chapter also explains why the WHERE clause is important for UPDATE and DELETE statements, and gives some guidelines that should be followed to ensure data security.

MySQL must know-20th Chapter-Update and delete data