sql

MySQL UPDATE Statement

In this example, I will demonstrate how to use the UPDATE statement to modify values using MySQL.

1. Introduction

MySQL is an open-source relational database management system. Here is the UPDATE statement syntax:

UPDATE {table_name} SET {assignment_list} [WHERE where_condition]
  • {table_name} – the table name.
  • {assignment_list} – one or more assignments which update records.
  • [WHERE where_condition] – optional condition.

2. Technologies Used

The example code in this article was built and run using:

  • MySQL

3. Set Up Table

In this step, I will create a table and insert three records.

create table scripts

create table user_tbl(
   user_id INT NOT NULL AUTO_INCREMENT,
   user_fname VARCHAR(100) NOT NULL,
   user_lname VARCHAR(40) NOT NULL,
   PRIMARY KEY ( user_id )
);

insert into user_tbl( user_fname, user_lname ) values('Mary','Zheng');
insert into user_tbl( user_fname, user_lname ) values('Tom2','Zheng');
insert into user_tbl( user_fname, user_lname ) values('Jerry2','Cheng');

4. Update Record

4.1 Update Single Record

Use a WHERE clause to limit the records. In this step, I will update the user_fname to “Test” for the user_id = 1.

update script – 1

update user_tbl 
set user_fname = 'Test'
where user_id = 1;

4.2 Update Multiple Records

In this step, I will update both user_fname and user_lname.

update script – 2

update user_tbl 
set user_fname = 'Test', user_lname='Chang'
where user_id = 1;

5. Update a New Column

In this step, I will add a new column via ALTER TABLE command and then update its value with two existing columns.

add a new column

ALTER TABLE user_tbl ADD login VARCHAR(100);

After the column is added, then use the UPDATE statement to change the value.

update script – 3

update user_tbl 
set login = CONCAT(user_fname , ".", user_lname)
where user_id > 0;

You can verify with a SELECT statement.

mysql update - update scripts
Figure 1 Update Scripts

6. Download the Source Code

Download
You can download the full source code of this example here: MySQL Update Statement

Mary Zheng

Mary has graduated from Mechanical Engineering department at ShangHai JiaoTong University. She also holds a Master degree in Computer Science from Webster University. During her studies she has been involved with a large number of projects ranging from programming and software engineering. She works as a senior Software Engineer in the telecommunications sector where she acts as a leader and works with others to design, implement, and monitor the software solution.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button