DEV Community

Cover image for How to update with inner join on PostgreSQL 🐘
Marcos Henrique
Marcos Henrique

Posted on

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

  public.payment p1
  staff_id = 3
  public.payment p2
 public.customer c ON c.fk_payment = p1.payment_id
  c.store_id = 2
  p1.payment_id = p2.payment_id;
Enter fullscreen mode Exit fullscreen mode

Second approach (Most commom way)

We use a subselect to do this

  public.payment p
  staff_id = 3
  SELECT fk_payment
  WHERE store_id = 2
= p.payment_id;
Enter fullscreen mode Exit fullscreen mode

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 🍻

Discussion (3)

vbilopav profile image

You could format a bit that sql

wakeupmh profile image
Marcos Henrique Author

Done 🀩

arthurbarbero profile image
Arthur Barbero

Glorious AndrΓ©!! The best man we have! Hahahaha