4 ms·
This is useful but the clunkyness of using FROM/WHERE vs JOIN ... USING has always annoyed me in Postgres, which they've based this on. The MySQL version is sor
by thom 6y ago
This is useful but the clunkyness of using FROM/WHERE vs JOIN ... USING has always annoyed me in Postgres, which they've based this on. The MySQL version is sort of what my brain expects.
- zeroimpl 6y agoI 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...