honeysql 2023-06-02 | Slack Archive

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-himik21:06:39

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

p-himik21:06:18

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

p-himik21:06:41

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

seancorfield01:06:22

@U0482NW9KL1 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 Ludwig03:06:02

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 Ludwig03:06:39

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

Samuel Ludwig03:06:42

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

seancorfield04:06:28

OK, keep us posted...

2023-06-02

Channels