Performing Updates with Joins in PostgreSQL
Basic UPDATE syntax:
The standard
UPDATE
statement looks like this:UPDATE table_name SET column_name = expression WHERE condition;
This updates the
column_name
intable_name
with the value ofexpression
based on thecondition
.Joining tables in UPDATE:
To join tables during an update, you introduce the joining table in the
FROM
clause after theSET
clause. TheWHERE
clause then specifies the condition for joining the tables.UPDATE target_table SET target_table.column = joined_table.column FROM joined_table WHERE target_table.join_column = joined_table.join_column;
target_table
: The table you want to update.joined_table
: The table containing the data used for the update.target_table.column
: The column in the target table being updated.joined_table.column
: The column in the joined table providing the update value.join_column
: The column used for joining the tables (present in both tables).
Example:
Imagine you have two tables:
customers
andorders
. You want to update theemail
in thecustomers
table based on email addresses stored in a separateemails
table.Here's the query:
UPDATE customers SET customers.email = emails.new_email FROM emails WHERE customers.customer_id = emails.customer_id;
This query updates the
email
column in thecustomers
table with the values from thenew_email
column in theemails
table, matching rows based on thecustomer_id
present in both tables.
Points to Remember:
- Ensure the
JOIN
condition produces a unique match for each row being updated. Otherwise, the update might behave unexpectedly. - Consider using subqueries if the logic requires more complex filtering or calculations before updating.
This example updates the net_price
of products in a products
table based on discounts in a product_segments
table.
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
price DECIMAL(10,2) NOT NULL
);
CREATE TABLE product_segments (
product_id INTEGER REFERENCES products(id),
segment_id INTEGER NOT NULL,
discount DECIMAL(5,2) NOT NULL,
PRIMARY KEY (product_id, segment_id)
);
-- Insert some sample data
INSERT INTO products (name, price) VALUES ('Shirt', 25.00), ('Hat', 15.00);
INSERT INTO product_segments (product_id, segment_id, discount)
VALUES (1, 1, 0.1), (2, 2, 0.05);
-- Update product net_price with discount applied
UPDATE products p
SET p.net_price = p.price * (1 - ps.discount)
FROM products p
JOIN product_segments ps ON p.id = ps.product_id;
Example 2: Updating customer contact information
This example updates phone numbers in a customers
table based on a temporary update table temp_contacts
.
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
phone_number VARCHAR(20)
);
CREATE TABLE temp_contacts (
customer_id INTEGER REFERENCES customers(id),
new_phone_number VARCHAR(20) NOT NULL
);
-- Insert some sample data
INSERT INTO customers (name, phone_number) VALUES ('John Doe', '123-456-7890');
INSERT INTO temp_contacts (customer_id, new_phone_number) VALUES (1, '555-555-5555');
-- Update customer phone numbers
UPDATE customers c
SET c.phone_number = tc.new_phone_number
FROM customers c
JOIN temp_contacts tc ON c.id = tc.customer_id;
Subqueries:
Subqueries allow you to write nested queries within the
UPDATE
statement'sWHERE
clause. This can be useful for complex filtering or calculations before updating the target table.Here's an example using the
customers
andemails
table scenario from before:UPDATE customers SET email = ( SELECT new_email FROM emails WHERE emails.customer_id = customers.customer_id ) WHERE customers.customer_id IN ( SELECT customer_id FROM emails WHERE new_email IS NOT NULL );
This approach retrieves the new email address from the
emails
table using a subquery and then updates thecustomers
table only for entries with a valid new email address (identified by the second subquery).Temporary Tables:
Temporary tables can be used as a staging area for data manipulation before updating the main table. This can be helpful for complex transformations or large datasets.
Here's an example using a temporary table to update product prices with discounts (similar to the first example with
UPDATE JOIN
):CREATE TEMP TABLE discounted_products ( product_id INTEGER, net_price DECIMAL(10,2) ); INSERT INTO discounted_products (product_id, net_price) SELECT p.id, p.price * (1 - ps.discount) FROM products p JOIN product_segments ps ON p.id = ps.product_id; UPDATE products SET price = dp.net_price FROM products JOIN discounted_products dp ON products.id = dp.product_id; DROP TABLE discounted_products;
This approach first calculates the discounted prices in a temporary table
discounted_products
and then updates theproducts
table using a separate update statement.
Choosing the best method depends on factors like:
- Complexity of filtering/calculations: Subqueries are suitable for complex logic within the update criteria.
- Data volume: For large datasets, temporary tables might be more efficient for intermediate data manipulation.
- Readability:
UPDATE JOIN
can be more readable for simpler updates referencing another table.
postgresql