SQL UPDATE Statement – Syntax, Examples

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:

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:

CREATE DATABASE school;

2. Select the school database:

USE school;

3. Create a table named students:

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:

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.

---

1 Updating a Single Column in a Specific Row

To update the locality of the student with id = 2:

UPDATE students
SET locality = 'Eastside'
WHERE id = 2;

This query updates the locality of the student named Bob from “Westend” to “Eastside”.

---

2 Updating Multiple Columns in a Single Row

To update both the grade and age of the student named Eva:

UPDATE students
SET grade = '10th', age = 16
WHERE name = 'Eva';

This query modifies two columns simultaneously, updating Eva’s grade to “10th” and age to 16.

---

3 Updating Multiple Rows Using a Condition

To update the locality of all students in the “8th” grade:

UPDATE students
SET locality = 'Central'
WHERE grade = '8th';

This query updates the locality to “Central” for all students in the 8th grade. Multiple rows are affected because of the condition.

---

4 Incrementing Values in a Column

To increment the age of all students by 1:

UPDATE students
SET age = age + 1;

This query increases the age of every student by 1 year. Be cautious with this type of update if no WHERE condition is specified, as it will affect all rows.

---

5 Updating All Rows in a Table

If you need to reset a value across all rows, you can omit the WHERE clause. For example, to reset all students’ grade to “Not Assigned”:

UPDATE students
SET grade = 'NA';

This query changes the grade of every student to “Not Assigned”. Note: Be careful with UPDATE statements without a WHERE clause, as it affects all rows.

---

FAQs for SQL UPDATE

1 What does the SQL UPDATE statement do?

The SQL UPDATE statement modifies existing data in a table. It allows you to change values in specific rows based on a condition or in all rows if no condition is specified.

2 Can I update multiple columns at once with SQL UPDATE?

Yes, you can update multiple columns by listing each column with its new value, separated by commas in the SET clause.

3 What happens if I use SQL UPDATE without a WHERE clause?

If you omit the WHERE clause, the UPDATE statement will apply the changes to all rows in the table, which may lead to unintended data modifications.

4 Can I use a subquery with SQL UPDATE?

Yes, you can use subqueries to update values conditionally based on the result of another query, which can be useful for more complex updates.

5 How do I increment a value in a column using SQL UPDATE?

To increment a value, you can use arithmetic operations in the SET clause. For example, SET age = age + 1 will add 1 to the age value in each selected row.