In PostgreSQL, the UPDATE statement is used to modify existing records in a database table.
Sometimes information stored in a database needs to be corrected or changed.
Real-life example:
Imagine a university database containing student records.
The university may need to:
We can perform these tasks using the UPDATE statement.
UPDATE table_name
SET column1 = value1,
column2 = value2
WHERE condition;
Understanding the syntax:
UPDATE: Specifies the table whose records we want to modify.table_name: Name of the table.SET: Specifies the columns and their new values.WHERE: Specifies which records should be updated.Let’s create a students table.
CREATE TABLE students (
student_id INTEGER PRIMARY KEY,
student_name VARCHAR(100),
email VARCHAR(100),
age INTEGER,
city VARCHAR(50),
fee NUMERIC(10, 2)
);
Insert sample records:
INSERT INTO students
(student_id, student_name, email, age, city, fee)
VALUES
(1, 'Ali', 'ali@example.com', 20, 'Multan', 50000),
(2, 'Ahmad', 'ahmad@example.com', 21, 'Lahore', 45000),
(3, 'Usman', 'usman@example.com', 22, 'Multan', 40000),
(4, 'Sara', 'sara@example.com', 19, 'Lahore', 55000),
(5, 'Ayesha', 'ayesha@example.com', 23, 'Multan', 45000);
View the records:
SELECT *
FROM students;
Suppose we want to change Ali’s city from Multan to Lahore.
UPDATE students
SET city = 'Lahore'
WHERE student_id = 1;
Understand the query:
UPDATE students selects the students table.SET city = 'Lahore' specifies the new city.WHERE student_id = 1 identifies the student whose record should be updated.Verify the change:
SELECT *
FROM students
WHERE student_id = 1;
We can update more than one column in a single query.
Example: Change Ahmad’s email address and city.
UPDATE students
SET
email = 'ahmad.new@example.com',
city = 'Multan'
WHERE student_id = 2;
This query updates both columns in the same student record.
We can use comparison operators in the WHERE clause.
Example 1: Increase the fee of students whose current fee is 40,000.
UPDATE students
SET fee = 45000
WHERE fee = 40000;
Example 2: Change the city of students older than 21 to Islamabad.
UPDATE students
SET city = 'Islamabad'
WHERE age > 21;
Example 3: Change the department or another column for students meeting a specific condition.
UPDATE students
SET fee = 50000
WHERE age >= 20;
This query updates the fee of every student aged 20 or above.
The AND operator allows us to apply multiple conditions.
Example: Update the fee of Computer Science students from Multan.
UPDATE students
SET fee = 55000
WHERE city = 'Multan'
AND student_id = 1;
Both conditions must be true for a record to be updated.
The OR operator allows us to update records that match at least one condition.
Example: Change the city of students whose IDs are 3 or 4.
UPDATE students
SET city = 'Islamabad'
WHERE student_id = 3
OR student_id = 4;
The IN operator makes it easier to match multiple values.
Example: Update the fee of students with IDs 1, 3, and 5.
UPDATE students
SET fee = 60000
WHERE student_id IN (1, 3, 5);
If we execute an UPDATE statement without a WHERE clause, PostgreSQL updates every record in the table.
Example:
UPDATE students
SET fee = 50000;
This query sets the fee of all students to 50,000.
Important warning
Always check the WHERE condition before executing an UPDATE query. Without it, all records may be modified unintentionally.
PostgreSQL provides the RETURNING clause to display the records affected by an UPDATE statement.
Example:
UPDATE students
SET city = 'Lahore'
WHERE student_id = 1
RETURNING *;
This query updates the student’s city and immediately displays the updated record.
You can also return selected columns:
UPDATE students
SET fee = 60000
WHERE student_id = 2
RETURNING student_id, student_name, fee;
We can perform mathematical calculations while updating values.
Example: Increase the fee of every student by 10%.
UPDATE students
SET fee = fee * 1.10;
Another example: Increase the age of a particular student by one year.
UPDATE students
SET age = age + 1
WHERE student_id = 1;
After updating records, use the SELECT statement to verify the changes.
SELECT *
FROM students;
To check a particular student:
SELECT *
FROM students
WHERE student_id = 1;
| Mistake | Explanation |
|---|---|
| Missing WHERE | All records may be updated. |
| Incorrect column name | The column must exist in the table. |
| Incorrect data type | The new value must be compatible with the column type. |
| Missing quotation marks | Text values require single quotation marks. |
| Incorrect condition | The wrong records may be updated. |
Incorrect example:
UPDATE students
SET city = 'Lahore';
This updates every student’s city, not just one student’s city.
Correct example:
UPDATE students
SET city = 'Lahore'
WHERE student_id = 1;
Try these queries using the students table.
Example 1: Change the city of student ID 2 to Multan.
UPDATE students
SET city = 'Multan'
WHERE student_id = 2;
Example 2: Change the email address of student ID 3.
UPDATE students
SET email = 'usman.new@example.com'
WHERE student_id = 3;
Example 3: Increase the fee of students by 5%.
UPDATE students
SET fee = fee * 1.05;
Example 4: Update students whose age is greater than 20.
UPDATE students
SET city = 'Islamabad'
WHERE age > 20;
Example 5: Update a record and display the result.
UPDATE students
SET fee = 65000
WHERE student_id = 4
RETURNING *;
Use the students table created in this lesson.
Exercise 1: Update Single Columns
Write queries to:
Exercise 2: Update Multiple Columns
Write queries to:
Exercise 3: Update Using Conditions
Write queries to:
Exercise 4: Use RETURNING
Write queries to:
| SQL Keyword | Purpose |
|---|---|
UPDATE |
Modifies existing records. |
SET |
Assigns new values to columns. |
WHERE |
Specifies which records to update. |
AND |
Requires all conditions to be true. |
OR |
Requires at least one condition to be true. |
IN |
Matches values from a list. |
RETURNING |
Displays updated records. |
Remember: Use WHERE carefully with UPDATE. Without a WHERE condition, all records in the table are updated.
Next Lesson: DELETE Statement in PostgreSQL
Students will learn how to delete specific records and remove all records from a table using the DELETE statement.