PostgreSQL Update with Join for an ObjectRocket Instance

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.