4 replies

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

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;

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

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...

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

users.subscriptionId

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;

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

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...

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

Similar Threads

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

Similar Threads

Similar Threads

Similar Threads