MySQL Tutorials for Business Analyst: MySQL UPDATE Query

Hits: 66

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_number full_names gender date_of_birth physical_address postal_address contct_number email
1 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
1 Janet Jones Female 21-07-1980 First Street Plot No 4 Private Bag 0759 253 542 janetjones@yagoo.cm

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 jj@fstreet.com

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.

 

Personal Career & Learning Guide for Data Analyst, Data Engineer and Data Scientist

Applied Machine Learning & Data Science Projects and Coding Recipes for Beginners

A list of FREE programming examples together with eTutorials & eBooks @ SETScholars

95% Discount on “Projects & Recipes, tutorials, ebooks”

Projects and Coding Recipes, eTutorials and eBooks: The best All-in-One resources for Data Analyst, Data Scientist, Machine Learning Engineer and Software Developer

Topics included: Classification, Clustering, Regression, Forecasting, Algorithms, Data Structures, Data Analytics & Data Science, Deep Learning, Machine Learning, Programming Languages and Software Tools & Packages.
(Discount is valid for limited time only)

Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.

Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners

Please do not waste your valuable time by watching videos, rather use end-to-end (Python and R) recipes from Professional Data Scientists to practice coding, and land the most demandable jobs in the fields of Predictive analytics & AI (Machine Learning and Data Science).

The objective is to guide the developers & analysts to “Learn how to Code” for Applied AI using end-to-end coding solutions, and unlock the world of opportunities!

 

MySQL Tutorials for Business Analyst: MySQL WHERE Clause with Examples – AND, OR, IN, NOT IN

Beginners Guide to SQL – SQL UPDATE Statement

Python Built-in Methods – Python Set update() Method