Fork me on GitHub
#honeysql
<
2022-05-17
>
vlad_poh13:05:59

How do i force "set" to show up after the join?

(hsql/format 
{:update [:customer :c]
    :join [[{:select [[[:min :c.row_start] :crs] :c.customerid]
             :from [[[:raw " customer for system_time all c"]]]
             :group-by [:customerid]} :x]
           [:= :c.customerid :x.customerid]]
    :set {:c.created :x.crs}}
generates the following. Query would work if the set clause appeared after the inner join
UPDATE customer c 
       SET c.created = x.crs 
INNER JOIN (  SELECT MIN(c.row_start) AS crs
                   , c.customerid 
                FROM  customer for system_time all c 
            GROUP BY customerid) AS x 
         ON c.customerid = x.customerid

seancorfield15:05:45

@U06GMV0B0 what version of HoneySQL? What database is this for?

vlad_poh16:05:38

@U04V70XH6 Maria DB : 10.6.7-MariaDB via Babashka (deps/add-deps '{:deps {com.github.seancorfield/honeysql {:mvn/version "2.2.861"}}})

seancorfield16:05:41

Ah, so it's a MySQL dialect -- you need to specify that, per the docs.

seancorfield16:05:38

MySQL (and MariaDB) has a different precedence for SET -- it's annoying (and it only shows up in complex UPDATE statements.

vlad_poh16:05:41

:man-facepalming: just saw it in the readme

seancorfield16:05:45

As I often say, there's a lot of good stuff in the docs :rolling_on_the_floor_laughing:

😅 1