PostgreSQL Update with Join for an ObjectRocket Instance
Introduction
In this article you will learn how to perform an join based update using a PostgreSQL instance on the ObjectRocket platform. What this means is that we are updating one table based on values in a separate table. If you’re still unclear on what that means it will become clear once you see the examples. Let’s jump into how to do a PostgreSQL update with join on the ObjectRocket platform.
NOTE: Once we connect to the ObjectRocket database the details of performing the update are the same as any PostgreSQL database and are not unique to ObjectRocket.
Prerequisites
You will need to set up the instance of PostgreSQL on your account and connect the PostgreSQL to your instance.
ObjectRocket Instance of PostgreSQL
Set up a PostgreSQL instance on your ObjectRocket account in the create instance button.
Connection of PostgreSQL to the database
Windows operating system you can use the use the psql command on the command prompt or the SQL Shell (psql) to connect to the ObjectRocket instance.
The following is an example of using the SQL Shell (psql) to connect to the ObjectRocket instance of PostgreSQL:
1 2 3 4 5 | Server [localhost]: ingress.w98sujpz.launchpad.objectrocket.cloud DATABASE [postgres]: Port [5432]: 4144 Username [postgres]: orkb Password FOR USER orkb: |
UNIX-based operating system you can use the psql command on the terminal window to connect to the ObjectRocket instance.
The following is an example of using the UNIX terminal window to connect to the ObjectRocket instance of PostgreSQL:
1 2 3 4 | psql -h ingress.w98sujpz.launchpad.objectrocket.cloud \ -U orkb \ -d postgres \ -p 4144 |
NOTE: The above connection examples are for demonstration purposes. Make sure to change the port and ingress URI so that they match your instance’s server. Navigate to Mission Control in a browser to get these important connection details for your Postgres instance.
PostgreSQL Update with Join
The PostgreSQL UPDATE statement is used to change the values on the table. By using the Join with UPDATE, you can update a values on a table from another table.
The following is the syntax for UPDATE JOIN:
1 2 3 4 | UPDATE table1 SET table1.column1 = new_value FROM table2 WHERE table1.column2 = table2.column2 |
Let’s look at an example for an UPDATE JOIN statement, but before that, let us create two tables named 'seller' and 'sold_products'.
PostgreSQL create table example
The following will create a table named 'seller' and 'sold_products':
1 2 3 4 5 6 7 8 9 10 11 12 13 | CREATE TABLE sellers ( id INT PRIMARY KEY, name VARCHAR, total_sold INT, total_earn NUMERIC ); CREATE TABLE sold_products ( id INT PRIMARY KEY, product VARCHAR, price NUMERIC, quantity INT, seller_id INT REFERENCES sellers (id) ); |
PostgreSQL insert example
Now that we have a table, let us now insert records to the tables.
The following will insert records to the tables:
1 2 3 4 5 6 7 8 9 10 11 12 13 | INSERT INTO sellers (id, name) VALUES ('1', 'Charles East'), ('2', 'Keziah Warren'), ('3', 'Natalie Sampson'); INSERT INTO sold_products VALUES ('1', 'Shoes', '30', '31', '2'), ('2', 'Necklace', '13.56', '23', '1'), ('3', 'Ring', '10', '7', '1'), ('4', 'Shampoo', '4.79', '11', '3'), ('5', 'Body Wash', '8.99', '12', '3'), ('6', 'Watch', '32.70', '25', '1'), ('7', 'Shirt', '12.89', '16', '2'), ('8', 'Soap', '6.00', '6', '3'); |
Postgres update & join examples
In our current table 'sellers', the columns 'total_sold' and 'total_earn' are NULL. To populate these will use the total sold_products and the total_ear of the seller from the 'sold_products' table.
1 2 3 4 | UPDATE sellers SET total_sold = quantity, total_earn = price FROM sold_products WHERE sellers.id = sold_products.seller_id; |
Let us look at our updated table using the SELECT statement. Notice how it copied the values from the other table, this is what an update based on a join can help with.
1 2 3 4 5 6 7 | SELECT * FROM sellers; id | name | total_sold | total_earn ----+-----------------+------------+------------ 2 | Keziah Warren | 31 | 30 1 | Charles East | 23 | 13.56 3 | Natalie Sampson | 11 | 4.79 (3 ROWS) |
Yes, the records were updated but if you notice it just copied the first value that matches the id of the seller. We’ll improve upon that and set the total_earn correctly. By using the SUM function, we will get the total number of sold products and the total amount earned but it is not permitted with the UPDATE statement. So instead we use the WITH query, see the following:
1 2 3 4 5 6 7 8 9 10 | WITH sold_products AS ( SELECT seller_id, SUM(quantity) AS ts, SUM(price * quantity) AS te FROM sold_products GROUP BY seller_id ) UPDATE sellers SET total_sold = ts, total_earn = te FROM sold_products WHERE sellers.id = sold_products.seller_id; |
Let’s take a look at the updated table by using select from * sellers' and see what our sellers table looks like now.
1 2 3 4 5 6 7 | SELECT * FROM sellers ; id | name | total_sold | total_earn ----+-----------------+------------+------------ 2 | Keziah Warren | 47 | 1136.24 1 | Charles East | 55 | 1199.38 3 | Natalie Sampson | 29 | 196.57 (3 ROWS) |
Now we have the total number of sold products and the total earnings of each sellers.
Conclusion
We hope this example of how to do a PostgreSQL update using join on the ObjectRocket platform has been informative. We used the ObjectRocket platform to do our demo but the sql commands used here should work on any PostgreSQL instance. If you don’t have a database already setup, ObjectRocket provides a free trial that is easy to setup and get started.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started