How to MySQL- Update & Delete statement
This MySQL tutorial explains how to use the MySQL UPDATE & DELETE Statements with syntax and examples. MySQL UPDATE command will modify any field value of any MySQL table.The DELETE statement is used to delete records in a table.
MySQL UPDATE Statement
The UPDATE statement is used to change or modify the existing records in a database table. It is typically used in conjugation with the WHERE clause to apply the changes to only those records that match specific criteria.
UPDATE table_name SET column1=value1, column2=value2,... WHERE column_name=some_value
column1, column2: Name of column(s) to be updated.
value1, value2: New value(s).
Consider the following “contacts” table inside the “lauyou” database:
The following example updates the email address of a contact record in the contacts table that matches firstname=’Anwar’ and lastname=’Muhammed’.
UPDATE contacts SET firstname.lastname@example.org' WHERE firstname='Anwar' AND lastname='Muhammed';
MySQL DELETE Statement
The MySQL DELETE statement is used to delete a single record or multiple records from a table in MySQL.
DELETE FROM table [WHERE conditions];
DELETE FROM contacts WHERE lastname = 'Yakkiparamban' AND firstname = 'Anwar';
This above example would delete all records from the contacts table where the lastname is Yakkiparamban and firstname is Anwar.
Following is the delete statement with full syntax:
DELETE [ LOW_PRIORITY ] [ QUICK ] [ IGNORE ] FROM table [WHERE conditions] [ORDER BY expression [ ASC | DESC ]] [LIMIT number_rows];
|LOW_PRIORITY||Optional. If LOW_PRIORITY is provided, the delete will be delayed until there are no processes reading from the table.|
|QUICK||Optional. If QUICK is provided, ndex leaves are not merged during the delete making the deletion faster for MyISAM tables.|
|IGNORE||Optional. If IGNORE is provided, all errors encountered during the delete are ignored.|
|table||The table that you wish to delete records from.|
|WHERE conditions||Optional. The conditions that must be met for the records to be deleted. If no conditions are provided, then all records from the table will be deleted.|
|ORDER BY expression||Optional. It may be used in combination with LIMIT to sort the records appropriately when limiting the number of records to be deleted.|
|LIMIT||Optional. If LIMIT is provided, it controls the maximum number of records to delete from the table. At most, the number of records specified by number_rows will be deleted from the table.|