2023-06-02 honeysql | Clojure Slack Archive

honeysql

Samuel Ludwig 2023-06-02T18:42:58.228309Z

attempting to create a multi-insert-on-duplicate-update mysql statement in the form of

INSERT INTO beautiful (name, age)
    VALUES
    ('Helen', 24),
    ('Katrina', 21),
    ('Samia', 22),
    ('Hui Ling', 25),
    ('Yumie', 29)
    AS newvals
ON DUPLICATE KEY UPDATE
    age = newvals.age
    ...

and am trying to understand what I might need to get that newvals values "alias" to generate I feel like I'm close, this is what I've got so far:

(def csvrows '(["id" "c1" "c2"] [1 "two" "four"] [2 "five" "six"]))
  (let [cols (first csvrows)]
    (-> (insert-into :mytable)
        (with-columns cols)
        (values (rest csvrows))
        (on-duplicate-key-update (zipmap cols (map (fn [x] (str "newvals." x)) cols)))
        (sql/format {:dialect :mysql}))))

Which produces

["INSERT INTO `mytable` (?, ?, ?) 
  VALUES (?, ?, ?), (?, ?, ?) 
  ON DUPLICATE KEY UPDATE 
  `id` = ?, `c1` = ?, `c2` = ?" 
 "id" "c1" "c2" 
 1 "two" "four" 
 2 "five" "six" 
 "newvals.id" "newvals.c1" "newvals.c2"]

p-himik 2023-06-02T21:25:39.827689Z

Use keywords instead of strings for identifiers such as table, column, or function names.

p-himik 2023-06-02T21:26:18.347309Z

For qualified identifiers, use either a dot directly or /, so either :newvals.age or :newvals/age. Nowadays, I myself prefer the latter.

p-himik 2023-06-02T21:49:41.394969Z

And if you need to construct such keywords at run time, it's easy to do with (keyword "newvals" "age").

seancorfield 2023-06-03T01:51:22.550289Z

@sludwig.dev did that answer your question? I'm not sure how you can create an alias in that position, mainly because I'm not sure which part of the statement the alias belongs to - I've never seen it in that position before.

Samuel Ludwig 2023-06-03T03:19:02.170329Z

apologies, i was left without computer access soon after tossing out that question :^) and that was a stack-overflow recommended solution for the case where one might want to make an INSERT+ON DUPLICATE KEY UPDATE on multiple inserts/rows at once, where in the case of a duplicate key, I'd want all values for that row to be updated to the new ones

Samuel Ludwig 2023-06-03T03:20:39.215509Z

SO post for reference: https://stackoverflow.com/questions/2714587/mysql-on-duplicate-key-update-for-multiple-rows-insert-in-single-query

Samuel Ludwig 2023-06-03T03:26:42.090069Z

i haven't gotten the chance to test himik's solution yet, but I don't think its exactly what im looking for

seancorfield 2023-06-03T04:10:28.176149Z

OK, keep us posted...

Clojurians Log v2

honeysql