Document/support Postgres's UPDATE SET FROM VALUES

[danielcompton]

Postgres has a way to run multiple UPDATEs in a single statement with different SET values: https://stackoverflow.com/a/26715934, https://www.postgresql.org/docs/current/sql-update.html

update users as u set -- postgres FTW
  email = u2.email,
  first_name = u2.first_name,
  last_name = u2.last_name
from (values
  (1, 'hollis@weimann.biz', 'Hollis', 'Connell'),
  (2, 'robert@duncan.info', 'Robert', 'Duncan')
) as u2(id, email, first_name, last_name)
where u2.id = u.id;

I tried to replicate this in HoneySQL and got pretty close, but wanted to check:

1. If there is a better way to write this
2. If this should be documented/tested somewhere

(sql/format {:update [:user :u]
             :set {:email :u2.email
                   :first_name :u2.first_name
                   :last_name :u2.last_name}
             :from [[{:values [[1 "hollis@weimann.biz" "Hollis" "Connell"]
                               [2 "robert@duncan.info" "Robert" "Duncan"]]}
                     [[:u2 :id :email :first_name :last_name]]]]
             :where [:= :u2.id :u.id]})
=>
["UPDATE user u SET email = u2.email, first_name = u2.first_name, last_name = u2.last_name FROM (VALUES (?, ?, ?, ?), (?, ?, ?, ?)) AS U2(id, email, first_name, last_name) WHERE u2.id = u.id"
 1 "hollis@weimann.biz" "Hollis" "Connell"
 2 "robert@duncan.info" "Robert" "Duncan"]

The main thing I'm concerned about is [[:u2 :id :email :first_name :last_name]]. I think I'm abusing the function call syntax to make a "table call"(?). This seems like it should work fine because SQL is case insensitive, but also feels a bit wrong.

as u2(id, email, first_name, last_name)
-- vs
AS U2(id, email, first_name, last_name)

[seancorfield]

See https://github.com/seancorfield/honeysql/issues/527 -- there's an example there, and the docs have been updated to refer to it (in the repo, not in a release yet). I probably need to add examples or at least cross-links from either update or from where it talks about values.

[danielcompton]

Thanks! I had checked the docs for UPDATE and searched the issue tracker but wasn't really sure what to search for.

[seancorfield]

I added that example from #527 under the UPDATE clause as well (it was already in the FROM clause section and the :composite special syntax section).