2 ms·
Personally I prefer UPDATE JOIN as I've used it in mysql: UPDATE inventory INNER JOIN (SELECT sum(quantity) AS amt, itemId FROM sales GROUP BY 2) AS daily
by Izkata 6y ago
Personally I prefer UPDATE JOIN as I've used it in mysql:
UPDATE inventory
INNER JOIN (SELECT sum(quantity) AS amt, itemId FROM sales GROUP BY 2) AS daily USING (itemId)
SET quantity = quantity - daily.amt;
I had thought postgres could do it this way without the extra FROM syntax, but apparently not.
- zeroimpl 6y agoThe idea of being able to update multiple tables in a single query this way is intriguing to me! But I like the fact that the Postgresql syntax makes it crystal clear which table is being updated.
- funny_falcon 6y agoIn fact, MySQL allows update of multiple tables. https://stackoverflow.com/questions/4361774/mysql-update-multiple-tables-with-one-query https://stackoverflow.com/questions/4361774/mysql-update-mul...