SQL UPDATE Statement

The SQL UPDATE statement is used to modify existing records in a table. Whether you want to change one row or multiple rows at once, the UPDATE statement provides a way to alter data in your database dynamically.

In this guide, we will cover the syntax, step-by-step explanations, and a range of examples to help you understand how to use UPDATE statement effectively.

Syntax of SQL UPDATE Statement

The basic syntax of an SQL UPDATE statement is as follows:

</>

Copy

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Each part of the syntax has a specific purpose:

UPDATE: Specifies the table in which you want to modify data.
SET: Lists the columns to be updated with their new values. Multiple columns can be updated in one statement, separated by commas.
WHERE: Filters the rows to be updated. Without a WHERE clause, all rows in the table will be updated, which can be risky and should be used with caution.

For safety, it’s best to always use a WHERE clause with UPDATE unless you intend to update all rows in the table.

Step-by-Step Examples with MySQL

We’ll go through various examples demonstrating the UPDATE statement in MySQL. Using MySQL 8.0 with MySQL Workbench, we’ll use a sample students table with fields id, name, age, grade, and locality.

Setup for Examples: Creating the Database and Table

1. Open MySQL Workbench and create a new database:

</>

Copy

CREATE DATABASE school;

2. Select the school database:

</>

Copy

USE school;

3. Create a table named students:

</>

Copy

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    grade VARCHAR(10),
    locality VARCHAR(50)
);

4. Insert some sample records to use in our examples:

</>

Copy

INSERT INTO students (name, age, grade, locality)
VALUES
('Alice', 14, '8th', 'Northside'),
('Bob', 15, '9th', 'Westend'),
('Charlie', 14, '8th', 'Northside'),
('David', 16, '10th', 'Southend'),
('Eva', 15, '9th', 'Westend');

Examples: Updating Records in the Table

Now, let’s explore different scenarios of updating records in this table.

TutorialKart

SQL UPDATE Statement – Syntax, Examples