Comment by zeroimpl | Hacker News Reader

Comment by zeroimpl | Hacker News Reader

Parent Full thread

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.

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.

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

In fact, MySQL allows update of multiple tables. https://stackoverflow.com/questions/4361774/mysql-update-mul...

Reply on news.ycombinator.com