Academic Block

Academic Block logo
Menu
SQL UPDATE
Learn how to modify existing records in database tables using the SQL UPDATE statement.

What is the UPDATE Statement?

The UPDATE statement is used to change existing data in one or more rows of a database table. You can update a single column, multiple columns, or several records at the same time.

The WHERE clause is especially important because it determines which rows will be modified. Without a WHERE clause, all rows in the table can be updated.

Basic UPDATE Syntax

The basic syntax uses SET to specify the new value and WHERE to identify the record that should be changed. 

CREATE TABLE planets (
  id INTEGER,
  name TEXT,
  distance INTEGER
);

INSERT INTO planets (id, name, distance)
VALUES
  (1, 'Mercury', 58),
  (2, 'Venus', 108),
  (3, 'Mars', 228);

UPDATE planets
SET distance = 227
WHERE name = 'Mars';

SELECT * FROM planets;

Updating a Single Column

You can change the value of one column for a specific record. 

CREATE TABLE cameras (
  id INTEGER,
  model TEXT,
  price REAL
);

INSERT INTO cameras (id, model, price)
VALUES
  (1, 'Alpha One', 45000),
  (2, 'Vision Pro', 52000),
  (3, 'Focus X', 38000);

UPDATE cameras
SET price = 42500
WHERE id = 3;

SELECT * FROM cameras;

Updating Multiple Columns

Multiple columns can be changed in the same UPDATE statement by separating assignments with commas. 

CREATE TABLE courses (
  id INTEGER,
  title TEXT,
  duration INTEGER,
  level TEXT
);

INSERT INTO courses (id, title, duration, level)
VALUES
  (1, 'Web Design', 6, 'Beginner'),
  (2, 'Data Science', 10, 'Intermediate'),
  (3, 'Robotics', 8, 'Beginner');

UPDATE courses
SET duration = 12,
    level = 'Advanced'
WHERE id = 2;

SELECT * FROM courses;

UPDATE with WHERE

The WHERE clause limits the update to rows that match a specified condition. 

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  available INTEGER
);

INSERT INTO books (id, title, available)
VALUES
  (1, 'Ocean Secrets', 1),
  (2, 'Hidden Valley', 1),
  (3, 'Winter Road', 0);

UPDATE books
SET available = 0
WHERE id = 2;

SELECT * FROM books;

Updating Multiple Rows

If several rows satisfy the WHERE condition, SQLite updates every matching row. 

CREATE TABLE products (
  id INTEGER,
  name TEXT,
  category TEXT,
  stock INTEGER
);

INSERT INTO products (id, name, category, stock)
VALUES
  (1, 'Desk Mat', 'Office', 20),
  (2, 'Pen Holder', 'Office', 15),
  (3, 'Water Bottle', 'Fitness', 30),
  (4, 'Notebook', 'Office', 25);

UPDATE products
SET stock = stock + 10
WHERE category = 'Office';

SELECT * FROM products;

UPDATE Using an Arithmetic Expression

You can use an existing column value in an expression to calculate the new value. 

CREATE TABLE tickets (
  id INTEGER,
  event_name TEXT,
  price REAL
);

INSERT INTO tickets (id, event_name, price)
VALUES
  (1, 'Music Night', 800),
  (2, 'Science Expo', 500),
  (3, 'Tech Summit', 1200);

UPDATE tickets
SET price = price + 100
WHERE event_name = 'Tech Summit';

SELECT * FROM tickets;

UPDATE with a Comparison

Comparison operators such as >, <, and = can be used in the WHERE clause. 

CREATE TABLE students (
  id INTEGER,
  name TEXT,
  score INTEGER,
  grade TEXT
);

INSERT INTO students (id, name, score, grade)
VALUES
  (1, 'Aisha', 88, 'B'),
  (2, 'Dev', 95, 'B'),
  (3, 'Mira', 72, 'C'),
  (4, 'Kabir', 91, 'B');

UPDATE students
SET grade = 'A'
WHERE score >= 90;

SELECT * FROM students;

UPDATE with AND

The AND operator can be used when multiple conditions must be satisfied. 

CREATE TABLE employees (
  id INTEGER,
  name TEXT,
  department TEXT,
  salary INTEGER
);

INSERT INTO employees (id, name, department, salary)
VALUES
  (1, 'Ravi', 'Sales', 42000),
  (2, 'Naina', 'Engineering', 65000),
  (3, 'Omar', 'Sales', 48000),
  (4, 'Tanya', 'Engineering', 72000);

UPDATE employees
SET salary = salary + 5000
WHERE department = 'Sales'
  AND salary < 45000;

SELECT * FROM employees;

UPDATE with OR

The OR operator allows an update when at least one of several conditions is true. 

CREATE TABLE movies (
  id INTEGER,
  title TEXT,
  genre TEXT,
  rating REAL
);

INSERT INTO movies (id, title, genre, rating)
VALUES
  (1, 'Blue Horizon', 'Drama', 7.2),
  (2, 'Star Mission', 'Science Fiction', 8.1),
  (3, 'Funny Days', 'Comedy', 6.8),
  (4, 'Deep Space', 'Science Fiction', 7.9);

UPDATE movies
SET rating = rating + 0.2
WHERE genre = 'Comedy'
   OR genre = 'Drama';

SELECT * FROM movies;

UPDATE NULL Values

You can use IS NULL to locate missing values and then update them with a new value. 

CREATE TABLE deliveries (
  id INTEGER,
  customer TEXT,
  tracking_code TEXT
);

INSERT INTO deliveries (id, customer, tracking_code)
VALUES
  (1, 'Ira', 'ZX100'),
  (2, 'Neel', NULL),
  (3, 'Sara', 'ZX300');

UPDATE deliveries
SET tracking_code = 'PENDING'
WHERE tracking_code IS NULL;

SELECT * FROM deliveries;

UPDATE Text Values

Text columns can be changed by assigning a new string value in the SET clause. 

CREATE TABLE cities (
  id INTEGER,
  name TEXT,
  country TEXT
);

INSERT INTO cities (id, name, country)
VALUES
  (1, 'Bengaluru', 'India'),
  (2, 'Pune', 'India'),
  (3, 'Lisbon', 'Portugal');

UPDATE cities
SET country = 'Portugal'
WHERE name = 'Lisbon';

SELECT * FROM cities;

UPDATE Using CASE

A CASE expression can assign different values depending on conditions. 

CREATE TABLE orders (
  id INTEGER,
  customer TEXT,
  amount REAL,
  category TEXT
);

INSERT INTO orders (id, customer, amount, category)
VALUES
  (1, 'Aarav', 450, 'Normal'),
  (2, 'Diya', 1800, 'Normal'),
  (3, 'Kabir', 3200, 'Normal'),
  (4, 'Rhea', 750, 'Normal');

UPDATE orders
SET category = CASE
  WHEN amount >= 2000 THEN 'Premium'
  WHEN amount >= 1000 THEN 'Large'
  ELSE 'Standard'
END;

SELECT * FROM orders;

UPDATE with LIKE

The LIKE operator can be used to update records whose text matches a particular pattern. 

CREATE TABLE products (
  id INTEGER,
  name TEXT,
  category TEXT
);

INSERT INTO products (id, name, category)
VALUES
  (1, 'Apple Juice', 'Other'),
  (2, 'Orange Juice', 'Other'),
  (3, 'Green Tea', 'Other'),
  (4, 'Apple Pie', 'Other');

UPDATE products
SET category = 'Fruit'
WHERE name LIKE 'Apple%';

SELECT * FROM products;

UPDATE with IN

The IN operator makes it easy to update rows whose value matches one of several specified values. 

CREATE TABLE languages (
  id INTEGER,
  name TEXT,
  status TEXT
);

INSERT INTO languages (id, name, status)
VALUES
  (1, 'Python', 'Basic'),
  (2, 'Java', 'Basic'),
  (3, 'SQL', 'Basic'),
  (4, 'C++', 'Basic');

UPDATE languages
SET status = 'Popular'
WHERE name IN ('Python', 'SQL');

SELECT * FROM languages;

UPDATE Without WHERE

If you omit the WHERE clause, SQLite updates every row in the table. This can be useful when intentionally changing a column for all records, but it should be used carefully. 

CREATE TABLE weather (
  city TEXT,
  unit TEXT
);

INSERT INTO weather (city, unit)
VALUES
  ('Delhi', 'C'),
  ('Mumbai', 'C'),
  ('Chennai', 'C');

UPDATE weather
SET unit = 'F';

SELECT * FROM weather;

UPDATE Using a Subquery

An UPDATE statement can use a subquery to determine the value that should be assigned to a column. 

CREATE TABLE employees (
  id INTEGER,
  name TEXT,
  department TEXT,
  salary INTEGER
);

INSERT INTO employees (id, name, department, salary)
VALUES
  (1, 'Maya', 'Design', 42000),
  (2, 'Arjun', 'Engineering', 65000),
  (3, 'Zara', 'Design', 46000);

UPDATE employees
SET salary = (
  SELECT MAX(salary)
  FROM employees
  WHERE department = 'Design'
)
WHERE name = 'Maya';

SELECT * FROM employees;

Common UPDATE Clauses

Clause Purpose Example
UPDATE Select the table to modify UPDATE products
SET Assign new values SET price = 500
WHERE Select rows to update WHERE id = 3
CASE Assign values conditionally CASE WHEN ... THEN ...
IS NULL Find missing values WHERE phone IS NULL

Advantages of UPDATE

  • Modifies existing records without creating new rows.
  • Can update one or multiple columns.
  • Can update one row or many matching rows.
  • Supports expressions and calculations.
  • Can use conditions such as AND, OR, IN, and LIKE.
  • Can handle NULL values and conditional updates.

Best Practices

  • Always check your WHERE condition before running an UPDATE.
  • Use a SELECT query first to verify which rows will be affected.
  • Do not omit WHERE unless you intentionally want to update every row.
  • Use precise conditions when modifying important data.
  • Use transactions when performing large or critical updates.
  • Back up important data before making large-scale changes.

The UPDATE statement allows you to modify existing data efficiently. Mastering SET, WHERE, conditions, expressions, and conditional updates will help you safely maintain SQL database records.

Ctrl + Enter to run SQL code Esc to close editor

🧪 Test Your SQL Code

Edit the SQL code on the left and click “Run Code” to see the result on the right.

📝 SQL Code
👁️ Preview (query result)

Click “Run Code” to see the result here.