Update with inner join?
Hey folks, is it possible to do an update with an inner join in order to have more complicated filter logic, with values coming from other tables, for our
where?5 Replies
So, you need to run an update statement with values from other tables?
Can you elaborate what you're trying to accomplish?
Sure! Essentially I want to update records with a WHERE clause that references another table
Typically in SQL I'd do
UPDATE entity INNER JOIN users ON users.id = entity.userId SET thing = 2 WHERE user.country = "CAN"
For instance, just typed from my phone
That or a subquery but doesn't look like that's possible in Drizzle either
So for now I'm doing two queries, one to get user IDs with the country and then an inArray... Not greatYou can put a subquery in
inArray
This is possible in one query
Just not with the join, has to be a subquery in the where clauseI'm just worried about the performance of a large inArray, but maybe I shouldn't?
Yeah, that's up for debate.
Drizzle doesn't support update join statements yet, so you'll have to write this in raw