3 ms·
It's for when you want to update more than one column with values from the same record in another table, eg: UPDATE books SET (title, isbn) = ( SELEC
by mjw1007 9y ago
It's for when you want to update more than one column with values from the same record in another table, eg:
UPDATE books
SET (title, isbn) = (
SELECT title, isbn FROM other_books
WHERE other_books.foo = books.bar);
You can also do it with UPDATE ... FROM (which may be more efficient), but that's a PostgreSQL extension, while the added-in-9.5 syntax is SQL standard.
UPDATE books
SET title = other_books.title, isbn=other_books.isbn
FROM other_books
WHERE other_books.foo = books.bar;
- colanderman 9y agoThat's not the row syntax though, that's the sub-select syntax (my initial confusion). i.e., there are three options according to the grammar: SET { column_name = { expression | DEFAULT } | ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) | ( column_name [, ...] ) = ( sub-SELECT ) } The second option, "row" syntax (to which the optional "ROW" keyword was recently added), doesn't allow for a row expression, only column expressions. The sub-select syntax suffices for row expressions (as your example demonstrates).