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.

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

Example:

Consider the following “contacts” table inside the “lauyou” database:

Contacts Table

MySQL Update & Delete Statements

 

The following example updates the email address of a contact record in the contacts table that matches firstname=’Anwar’ and lastname=’Muhammed’.

Update Query
     UPDATE contacts 
     SET email='info@lauyou.com' 
     WHERE firstname='Anwar' AND lastname='Muhammed';

 

Output

MySQL Update & Delete Query

 

MySQL DELETE Statement

The MySQL DELETE statement is used to delete a single record or multiple records from a table in MySQL.

Syntax
     DELETE FROM table
     [WHERE conditions];
Example
     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.

 

Output

MySQL Update & Delete Query

 

Following is the delete statement with full syntax:

Syntax
     DELETE [ LOW_PRIORITY ] [ QUICK ] [ IGNORE ] FROM table
     [WHERE conditions]
     [ORDER BY expression [ ASC | DESC ]]
     [LIMIT number_rows];

 

 Parameters:

ParametersDescription
LOW_PRIORITYOptional. If LOW_PRIORITY is provided, the delete will be delayed until there are no processes reading from the table.
QUICKOptional. If QUICK is provided, ndex leaves are not merged during the delete making the deletion faster for MyISAM tables.
IGNOREOptional. If IGNORE is provided, all errors encountered during the delete are ignored.
tableThe table that you wish to delete records from.
WHERE conditionsOptional. 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 expressionOptional. It may be used in combination with LIMIT to sort the records appropriately when limiting the number of records to be deleted.
LIMITOptional. 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.

Anwar Yakkiparamban

Anwar Yakkiparamban is the founder of Lauyou Learning. Prior to Lauyou learning, Anwar worked at ARD Engineering & Development, Qatar. He holds bachelor degree in Electronics and Communication Engineering from Govt. Engineering College Idukki.

You may also like...