Slick 3 is generating incorrect MySQL upsert statements for a single column table

Question

I have a very simple MySQL table which represents a set of names using a single String column. I want to use Slick's insertOrUpdate but it is generating incorrect MySQL, causing errors. Specifically, it wants to execute

insert into `TABLE1_NAME` (`column`)  values ('value') on duplicate key update

It doesn't specify what to update, so this fails. A similar table with two columns upserts fine, with statements like

insert into `TABLE2_NAME` (`key`, `other_col`)  values ('value1', 'value2') on duplicate key update `other_col`=VALUES(`other_col`)

Has anyone seen this? We do set a primary key for TABLE1. We may be doing our table projection mapping incorrectly. We're using Slick 3.1.1.

class Table1(tag: Tag) extends Table[Table1Record](tag, "TABLE1_NAME") {
  def value = column[String]("value", O.PrimaryKey, O.Length(254))
  def * = (value) <> (Table1Record, Table1Record.unapply)
}

class Table2(tag: Tag) extends Table[Table2Record](tag, "TABLE2_NAME") {
  def value1 = column[String]("value1", O.PrimaryKey, O.Length(254))
  def value2 = column[String]("value2", O.Length(254))
  def * = (value1, value2) <> (Table2Record.tupled, Table2Record.unapply)
}

Ken Bloom · Accepted Answer · 2016-04-04T18:39:51.290

There's no such concept of "insert or update" into a single column table. The single column is part of the key. If the key matches exactly, then are no other columns to update. If the key didn't match, then the newly inserted row won't be a duplicate of any key, so the update clause won't happen. Because there are no other columns to update, the generated SQL is malformed -- a bit of text has been generated with the assumption that some field names would be appended after it, but there were no field names to append.

By the way, for a table with two columns, the insert statement looks like

insert into `TABLE2_NAME` (`key`, `other_col`) 
values ('value1', 'value2')
on duplicate key update `other_col`=VALUES(`other_col`)

It lists only the non-key columns in the update clause. (Getting this correct should help you to better understand what's going on with your single-column table.)

I see. So what I really want is: "insert if not exists" like in [this question](https://stackoverflow.com/questions/30706193/insert-if-not-exists-in-slick-3-0-0). Thanks! — broadmonkey, Apr 04 '16 at 19:17

Slick 3 is generating incorrect MySQL upsert statements for a single column table

1 Answers1