How to update with inner join on PostgreSQL 🐘

What is PostgreSQL?🤔

In short words PostgreSQL is a relational object database management system (DBMS) developed as an open source project, If you want know deeply I recommends to click here.

Let's go to what matters 🤗

First of all the biggest spoiler possible and the sad reality, we can't do this ☹

But calm down, we are programmers and for us everything has a way 😅

The possibilites to get around this 👁

Before we approach the possibilities, let's understand why this happens, since in MySQL we can easily perform this operation.
This is because PostgreSQL uses the ansi-86 joins syntax in update, MySQL uses the ansi-92 syntax.

Let's assume we have two tables: customer and payment, and in this scenario we want to update a value in the payment table where customer fk in the payment table is equal to customer id and the store_id is 2, setting 33 to staff_id.

I found 2 approachs of a solution 😬

First approach

This solution was created by glorious André, in this solution we need to force the scope of the update in postgreSQL because the first part of the update is an isolated scope of the second part, so we will have two different aliases for the same table, not the most elegant but solves our need

UPDATE
  public.payment p1
SET
  staff_id = 3
FROM
  public.payment p2
INNER JOIN
 public.customer c ON c.fk_payment = p1.payment_id
WHERE
  c.store_id = 2
AND
  p1.payment_id = p2.payment_id;

Second approach (Most commom way)

We use a subselect to do this

UPDATE
  public.payment p
SET
  staff_id = 3
WHERE
(
  SELECT fk_payment
  FROM 
    public.customer
  WHERE store_id = 2
)
= p.payment_id;

Despite the limitation that postgreSQL imposes on us we can do a smart update

Thanks for reading and if you know other ways, share with us in the comments 🍻

Comments

[vbilopav]

You could format a bit that sql

[Marcos Henrique]

Done 🤩

[Arthur Barbero]

Glorious André!! The best man we have! Hahahaha