UPDATE Query in sql

Donald Green Donald Green
Feb 4, 2022 - 18:50
0 20
UPDATE Query in sql

What is an UPDATE request?

UPDATE The MySQL command is used to change rows in a table. The update command can be used to update one 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 for an update query in MySQL is shown below.

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

HERE

  • UPDATE `table_name` is a command that tells MySQL to update the data in a table.
  • SET `column_name` =` new_value ' are the names and values ​​of the fields affected by the update request. Note that when setting update values, string data types must be in single quotes. Numeric values ​​must not be in quotes. The date data type must be in single quotes and in the format "YYYY-MM-DD".
  • The [WHERE condition] is optional and can be used to set a filter that limits the number of rows affected by a MySQL UPDATE query.

Update in MySQL example

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

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

We will start with an update for membership #1, before we make any updates to our data, let's restore the record for membership #1. The script shown below helps us with this.

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

 

Executing the above script gives us the following results.

membership_number full_names gender date_of_birth physical_address postal_address contct_number email
one Janet Jones Female 21-07-1980 First Street Plot No 4 Private Bag 999 janetjones@yagoo.cm

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_number full_names gender date_of_birth physical_address postal_address contct_number email
one Janet Jones Female 21-07-1980 First Street Plot No 4 Private Bag 0759 253 542 <a href='mailto:janetjones@yagoo ;.cm'>janetjones@yagoo.cm</a>

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

membership_number full_names gender date_of_birth physical_address postal_address contct_number email
2 Smith Jones Female 23-06-1980 park street NULL NULL jj@fstreet.com

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_number full_names gender date_of_birth physical_address postal_address contct_number email
2 Janet Smith Jones Female 23-06-1980 Melrose 123 NULL NULL <a href='mailto:jj@fstreet.com'>jj@fstre et.com</a>

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.