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.
6. Download the Source Code
You can download the full source code of this example here: MySQL Update Statement
