Public Vars

Back

on-duplicate-key-update (clj)

(source)

function

(on-duplicate-key-update column-value-map)
MySQL's upsert facility. Accepts a hash map of column/value pairs to be updated (like `set` does).

Examples

honeysql
(ns honey.sql.helpers-test
  (:refer-clojure :exclude [filter for group-by partition-by set update])
  (:require [clojure.test :refer [deftest is testing]]
            [honey.sql :as sql]
            [honey.sql.helpers :as h
             :refer [add-column add-index alter-table columns create-table create-table-as create-view
                     create-materialized-view drop-view drop-materialized-view
                     create-index
                     bulk-collect-into
                     cross-join do-update-set drop-column drop-index drop-table
                     filter from full-join
                     group-by having insert-into
                     join-by join lateral left-join limit offset on-conflict
                     on-duplicate-key-update
                     order-by over partition-by refresh-materialized-view
                     rename-column rename-table returning right-join
                     select select-distinct select-top select-distinct-top
                     values where window with with-columns
                     with-data within-group]]))

(deftest mysql-on-duplicate-key-update
  (testing "From https://www.mysqltutorial.org/mysql-insert-or-update-on-duplicate-key-update"
    (is (= (sql/format (-> (insert-into :device)
                           (columns :name)
                           (values [["Printer"]])
                           (on-duplicate-key-update {:name "Printer"})))
           ["INSERT INTO device (name) VALUES (?) ON DUPLICATE KEY UPDATE name = ?"
            "Printer" "Printer"]))
    (is (= (sql/format (-> (insert-into :device)
                           (columns :id :name)
                           (values [[4 "Printer"]])
                           (on-duplicate-key-update {:name "Central Printer"})))
           ["INSERT INTO device (id, name) VALUES (?, ?) ON DUPLICATE KEY UPDATE name = ?"
            4 "Printer" "Central Printer"]))
    (is (= (sql/format (-> (insert-into :table)
                           (columns :c1)
                           (values [[42]])
                           (on-duplicate-key-update {:c1 [:+ [:values :c1] 1]})))
           ["INSERT INTO table (c1) VALUES (?) ON DUPLICATE KEY UPDATE c1 = VALUES(c1) + ?"
            42 1]))))