In PostgreSQL, updating multiple rows in a single query is done using the UPDATE keyword. 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. It’s a crucial component of maintaining data integrity and ensuring that your database remains up-to-date with accurate 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: First you have to create a database, to create a database write the following query. I’m naming my database “employee_database”, you can name your database whatever you want.
Query:
CREATE DATABASE employee_database;
Step 2: 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 databasename
Step 3: 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: 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:
Insert Data
Using VALUES Clause in a FROM Statement
step 5: 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.
Syntax:
UPDATE table_name
SET column1 = value1,
column2 = value2,
column3 = value3,
...
WHERE condition;
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:
Updated Data
Using CASE Statement
Step 6: You can also use the the keyword UPDATE and WHEN to update the values. For example a condition within the CASE statement. If the value in column_name matches ‘column_name1’, then it updates column_value to column_value1. 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.
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');
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:
Updated Data
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. 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.