Query GUI Mode: Bulk Update Via Composite Primary Key

Presently, the GUI mode only let's you use bulk update if your table has a single-column primary key in MySQL. However, for some data sets a composite primary key is the choice we have available. Using only a single column for the update would not correctly identify the record to update. Take the following simple example:

CREATE TABLE foo (
  user_id int(10) unsigned not null,
  location_id int(10) unsigned not null,
  rating int(3) unsigned not null,
  PRIMARY KEY(user_id, location_id)
)

In this case, I'd like to update based on the tuple of user_id and location_id, either one is insufficient to identify the record on its own.

@church this is a great request. For anyone viewing, this thread has some more context on what Retool current does/doesn't support: How to use composite primary key for bulk updates?

Thanks Justin, I, of course, do use the loop around a sql query via js query pattern, since I don't want to turn off placeholder parsing for the whole database connection (which would let me generate all the updates using INSERT ... ON DUPLICATE KEY UPDATE by quickly crafting a string with parenthetical lists).

However, it's a lot of work relative to the GUI mode, and enabling composite keys in bulk upserts via the GUI is much easier on me =)

1 Like