How to Update Multiple Rows in PostgreSQL?
In PostgreSQL, the UPDATE statement is a powerful tool used to modify existing records within a table. We appoint two sorts of examples: the primary includes updating based totally on a single condition, while the second relates to updating based totally on multiple conditions.
Throughout this article, we can make use of PostgreSQL Server as our database and reference the SELECT keyword for illustrative purposes.
Update Multiple Rows in PostgreSQL
In PostgreSQL, the UPDATE statement is a powerful tool used to modify existing records within a table. It allows you to make precise changes to specific columns of one or more rows based on specified conditions. When using the UPDATE statement, you first identify the table you want to update, and then you set the new values for the desired columns using the SET clause.
Conditions for which rows to update are specified in the WHERE clause, ensuring that only the rows meeting those criteria are modified. This statement enables you to efficiently update data, whether it's adjusting employee salaries, changing positions, or updating other pertinent information. Additionally, careful consideration should be given to crafting update statements to avoid unintended changes and maintain data consistency.
Syntax:
UPDATE table_name
SET column1 = value1,
column2 = value2,
column3 = value3,
...
WHERE condition;
Steps to Update Multiple Rows in PostgreSQL
Step 1: Create a Database
First, create a database using the following query. We'll name our database "employee_database," but you can choose any name you prefer.
Query:
CREATE DATABASE employee_database;
Step 2: Use the Database
Now that your database is created, you have to use the data database that you have just created. To use the database that you have just created you will have to use the following query.
Query:
\c employee_databaseStep 3: Create a Table
Create a table of employee inside the database employee_database. This table has 6 columns namely 'employee_id', 'name', 'email', 'salary', 'position', from the 'department' containing the id of the employee, name of the employee, email address of the employee, salary of the employee, position of the employee and the department in which the employee works in.
Query:
CREATE TABLE employee (
employee_id INTEGER,
name VARCHAR(100),
email VARCHAR(100),
salary DECIMAL(10, 2),
position VARCHAR(100),
department VARCHAR(100)
);
Step 4: Insert Data into the Table
After the table is created, now add data into your table using the query below.
Query:
INSERT INTO employee (employee_id, name, email, salary, position, department)
VALUES
(101, 'John Doe', 'john.doe@example.com', 50000.00, 'Software Engineer', 'Engineering'),
(102, 'Jane Smith', 'jane.smith@example.com', 60000.00, 'Data Analyst', 'Analytics'),
(103, 'Michael Johnson', 'michael.johnson@example.com', 70000.00, 'Product Manager', 'Product Management'),
(104, 'Emily Davis', 'emily.davis@example.com', 55000.00, 'Marketing Manager', 'Marketing'),
(105, 'David Lee', 'david.lee@example.com', 48000.00, 'Customer Support Specialist', 'Customer Service'),
(106, 'Sophia Garcia', 'sophia.garcia@example.com', 62000.00, 'Financial Analyst', 'Finance'),
(107, 'Matthew Wilson', 'matthew.wilson@example.com', 53000.00, 'HR Manager', 'Human Resources'),
(108, 'Olivia Brown', 'olivia.brown@example.com', 58000.00, 'Sales Representative', 'Sales'),
(109, 'Daniel Martinez', 'daniel.martinez@example.com', 54000.00, 'Operations Coordinator', 'Operations'),
(110, 'Ava Taylor', 'ava.taylor@example.com', 60000.00, 'Business Analyst', 'Business Development');
Output:
Methods to Update Multiple Rows in PostgreSQL
Once, the table has been created, we can now use two ways to update multiple rows in PostgreSQL. These are:
- Using VALUES Clause in a FROM Statement
- Using CASE Statement
1. Updating Multiple Rows in PostgreSQL using VALUES Clause
Now that you have inserted the data into your table, you now want to update some data from the employees table at once. To update multiple rows of your table at once you have to use the query below. UPDATE statement modifies existing records in a table by setting new values to specified columns. You can filter which records to update using the WHERE clause based on certain conditions.
Query:
UPDATE employee
SET salary = data.new_salary
FROM (
VALUES
('John Doe', 'Senior Software Engineer', 52000.00),
('Jane Smith', 'Senior Data Analyst', 61000.00),
('Michael Johnson', 72000.00),
('Emily Davis', 56000.00),
('David Lee', 49000.00),
('Sophia Garcia', 63000.00),
('Matthew Wilson', 54000.00),
('Olivia Brown', 59000.00),
('Daniel Martinez', 55000.00),
('Ava Taylor', 'Senior Business Analyst', 61000.00)
) AS data(name, position, new_salary)
WHERE employee.name = data.name;
Output:
2. Updating Multiple Rows in PostgreSQL using CASE Statement
You can also use the CASE statement within the UPDATE statement to conditionally update values. If the value in column_name matches 'column_name1', then it updates column_value to column_value1.
Syntax:
UPDATE table_name
SET column_name =
CASE column_name
WHEN 'column_name1' THEN column_value1
WHEN 'column_name2' THEN column_value2
ELSE column_value
END
WHERE column_name IN ('column_name1', 'column_name2');
where,
- ELSE column_value: This part of the CASE statement specifies the default value for column_value if none of the previous conditions are met.
- END: This closes the CASE statement.
Query:
UPDATE Department
SET Position =
CASE employee_id
WHEN 101 THEN 'Senior Software Engineer'
WHEN 102 THEN 'Senior Data Analyst'
WHEN 103 THEN 'Senior Product Manager'
ELSE 'Position Not Uptated'
END;
Output:
Conclusion
In PostgreSQL, the UPDATE statement is a powerful tool used to modify existing records within a table. It allows you to make precise changes to specific columns of one or more rows based on specified conditions.
By carefully crafting UPDATE statements, you can efficiently update data, ensuring that your database remains accurate and up-to-date. This is essential for tasks such as adjusting employee salaries, changing positions, or updating other pertinent information. Always consider the conditions and structure of your UPDATE statements to maintain data consistency and integrity.
