Support for UPDATE SET ... FROM (VALUES ...) pattern

[vemv]

Today I found using myself a pattern for which honeysql support wasn't immediately apparent:

  UPDATE membership
  SET last_active_at = new_values.last_active_at
  FROM (
    VALUES
        (?, ?, ?),
        ...
        (?, ?, ?)
        ) AS new_values (last_active_at, user_id, team_id)
  WHERE membership.user_id = new_values.user_id
    AND membership.team_id = new_values.team_id

The intent of such queries is that the parameters can combine "what to persist" with "what to query".

(In specific terms: new_values.last_active_at is something to persist, new_values.user_id/new_values.team_id is something to query)

Is this something trivially obtainable with Honey as-is, or would it need some adaptation?

I gave it a few attempts, to no avail.

Thanks - V

[vemv]

Just in case - I edited the example/wording as it wasn't originally as clear.

[seancorfield]

I think this produces what you want?

  (sql/format {:update :membership
               :set {:last-active-at :new-values.last-active-at}
               :from [[{:values [[1 2 3]
                                 [4 5 6]]} 
                       [:new-values [:composite :last-active-at :user-id :team-id]]]]
               :where [:and
                       [:= :membership.user-id :new-values.user-id]
                       [:= :membership.team-id :new-values.team-id]]})

[vemv]

Certainly - thanks much!

I had searched for a similar construction in clause-reference.md and honey.sql-test. There doesn't appear to be one.

I also noticed now that :composite has no test coverage - perhaps good chance to kill two birds with one stone?

[seancorfield]

I had searched for a similar construction in clause-reference.md and honey.sql-test. There doesn't appear to be one.

Probably because I didn't know such a syntactic construct was possible. I've added the "documentation" tag to add an example in the clause reference.

I also noticed now that :composite has no test coverage - perhaps good chance to kill two birds with one stone?

I've added the "needs test" tag to remind me to add some tests for it (but it's a pretty trivial syntax expansion).

[vemv]

Thanks, Sean!

[vemv]

Awesome! That seems easily grepable for anyone checking out the repo.