What is the UPDATE Query?

UPDATE MySQL command is used to modify rows in a table. The update command can be used to update a single field or multiple fields at the same time. It can also be used to update a MySQL table with values from another table.

MySQL Update Command Syntax

The basic syntax of the Update query in MySQL is as shown below.

UPDATE `table_name` SET `column_name` = `new_value' [WHERE condition];

HERE

  • UPDATE `table_name` is the command that tells MySQL to update the data in a table .
  • SET `column_name` = `new_value' are the names and values of the fields to be affected by the update query. Note, when setting the update values, strings data types must be in single quotes. Numeric values do not need to be in quotation marks. Date data type must be in single quotes and in the format 'YYYY-MM-DD'.
  • [WHERE condition]  is optional and can be used to put a filter that restricts the number of rows affected by the UPDATE MySQL query.

Update in MySQL Example

Let's now look at a practical example that updates data in the members table. Let's suppose that our member's membership numbers 1 and 2 have the following updates to be made to their data records.

Membership number Updates required
1 Changed contact number from 999 to 0759 253 532
2 Change the name to Janet Smith Jones and physical address should be updated to Melrose 123

We will start with making updates for membership number  1 before we make any updates to our data, let's retrieve the record for membership number 1. The script shown below helps us to do that.

SELECT * FROM `members` WHERE `membership_number` = 1;

 

Executing the above script gives us the following results.

membership_numberfull_namesgenderdate_of_birthphysical_addresspostal_addresscontct_numberemail
1Janet JonesFemale21-07-1980First Street Plot No 4Private Bag999This email address is being protected from spambots. You need JavaScript enabled to view it.

Let's now update the contact number using the script shown below.

UPDATE `members` SET `contact_number` = '0759 253 542' WHERE `membership_number` = 1;
 

Executing the above script updates the contact number from 999 to 0759 253 532 for membership number 1. Let's now look at the record for membership number 1 after executing the update script.

 
SELECT * FROM `members` WHERE `membership_number` = 1;

Executing the above script gives us the following results.

membership_numberfull_namesgenderdate_of_birthphysical_addresspostal_addresscontct_numberemail
1Janet JonesFemale21-07-1980First Street Plot No 4Private Bag0759 253 542This email address is being protected from spambots. You need JavaScript enabled to view it.

Let's now look at the updates required for membership number 2.

membership_numberfull_namesgenderdate_of_birthphysical_addresspostal_addresscontct_numberemail
2Smith JonesFemale23-06-1980Park StreetNULLNULLThis email address is being protected from spambots. You need JavaScript enabled to view it.

The following script helps us to do that.

 
UPDATE `members` SET `full_names` = 'Janet Smith Jones', `physical_address` = 'Melrose 123' WHERE `membership_number` = 2;

Executing the above script in updates the full names for membership number 2 to Janet Smith Jones and the physical address to Melrose 123.

membership_numberfull_namesgenderdate_of_birthphysical_addresspostal_addresscontct_numberemail
2Janet Smith JonesFemale23-06-1980Melrose 123NULLNULLThis email address is being protected from spambots. You need JavaScript enabled to view it.

Summary

  • The update command is used to modify existing data.
  • The "WHERE clause" is used to limit the number of rows affected by the UPDATE query.

 

YOU MIGHT LIKE:
SQLite

SQLite PDF

SQLite is an open-source, embedded, relational database management system, designed circa 2000. It is...