4 ms·
I wonder if they could just make this syntax work in Postgres: UPDATE inventory SET quantity = inventory.quantity - daily.amt FROM (SELECT
by zeroimpl 6y ago
I wonder if they could just make this syntax work in Postgres:
UPDATE inventory
SET quantity = inventory.quantity - daily.amt
FROM
(SELECT sum(quantity) AS amt, itemId FROM sales GROUP BY 2) daily USING( itemId );
Basically just allowing a ON/USING clause after the first FROM entry as if it was joining to the table being updated.
Otherwise it's kind of annoying when you end up joining to several tables in the update, but have to use the WHERE clause to join back to the main table.
- Izkata 6y agoPersonally 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...