ON DUPLICATE KEY UPDATE clause for batch INSERTS

[ghost]

In MySQL it is possible to use ON DUPLICATE KEY UPDATE clause in batch inserts, like in the below example:

INSERT INTO `buoy_stations` (`id`, `coords`, `name`, `owner`, `pgm`, `met`, `currents`)
VALUES 
('00922', 'Point(30,-90)','name 1','owner 1','pgm 1','y','y'),
('00923', 'Point(30,-90)','name 2','owner 2','pgm 2','y','y'),
('00924', 'Point(30,-90)','name 3','owner 3','pgm 3','y','y'),
('00925', 'Point(30,-90)','name 4','owner 4','pgm 4','y','y'),
('00926', 'Point(30,-90)','name 5','owner 5','pgm 5','y','y')
ON DUPLICATE KEY
        UPDATE coords=values(coords), name=values(name), owner=values(owner), pgm=values(pgm), met=values(met), currents=values(currents);

However, it appears that it is impossible to perform batch inserts with go-jet's current ON DUPLICATE KEY UPDATE implementation.

[go-jet]

VALUES is currently not supported. There is a workaround using Raw operator:

.ON_DUPLICATE_KEY_UPDATE(
	Link.ID.SET(Link.ID.ADD(Int(11))),
	Link.Name.SET(StringExp(Raw("values(name)"))),
)

[ghost]

Thanks for the tip @go-jet. It certainly gets the job done, however I find it very tedious to write queries that way. Are there any plans on adding VALUES support?

[go-jet]

Yep, it will be added eventually.

[ghost]

Hello @go-jet , I hope you are doing well. This is to remind you that there still is interest in this important (in my opinion) enhancement.

[go-jet]

values operator will likely not be supported as both MySQL and MariaDB plan to deprecate it.
MySQL new approach is using insert row alias. Support for mysql insert row alias is included in v2.9.0 release.

Code sample:

stmt := Link.INSERT().
	MODELS([]model.Link{
		{
			ID:          randId,
			URL:         "https://www.postgresqltutorial.com",
			Name:        "PostgreSQL Tutorial",
			Description: nil,
		},
		{
			ID:          randId,
			URL:         "https://www.yahoo.com",
			Name:        "Yahoo",
			Description: testutils.StringPtr("web portal and search engine"),
		},
	}).AS_NEW().
	ON_DUPLICATE_KEY_UPDATE(
		Link.ID.SET(Link.ID.ADD(Int(11))),
		Link.URL.SET(Link.NEW.URL),
		Link.Name.SET(Link.NEW.Name),
		Link.Description.SET(Link.NEW.Description),
	)