Drizzle TeamDT
Drizzle Team•2y ago
Eddy Vinck

How to do an UPDATE with a JOIN in Drizzle ORM?

Something like this

https://stackoverflow.com/questions/7869592/how-to-do-an-update-join-in-postgresql

I have this schema where I want to do an update on the "user_usage" table, but only if users.subscriptionId is a certain string or in a set of string values .

I think what I want here is an UPDATE with a JOIN. Could not quite figure out how to do this with Drizzle ORM.

Something like this from StackOverflow:

UPDATE table_1 t1
SET foo = 'new_value'
FROM table_2 t2
    JOIN table_3 t3 ON t3.id = t2.t3_id
WHERE
    t2.id = t1.t2_id
    AND t3.bar = True;


But maybe a common table expression (CTE) could work like this example from StackOverflow?

with t as (
    select v.id as rowid, s.price_per_vehicle as calculatedvalue
    from vehicles_vehicle v 
    join shipments_shipment s on v.shipment_id = s.id 
)
update vehicles_vehicle
set price = t.calculatedvalue
from t
where id = t.rowid


Schema in next post
Stack Overflow
How to do an update + join in PostgreSQL?
Basically, I want to do this:

update vehicles_vehicle v
join shipments_shipment s on v.shipment_id=s.id
set v.price=s.price_per_vehicle;
I'm pretty sure that would work in MySQL (my backgro...
Was this page helpful?