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.
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 😅
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.
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;
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 🍻