Bulk updates without PK help

I have some database tables without a PK and I need to update those tables through Retool. Users click the database table name populated in this inventory lookup table which then opens the database table of the same name (in a Retool table component called targetTableUpdate. (click on BRYAN TESTING and it will open that database table ees_bryan_testing_v11, which is what we’re trying to modify) or click on another row for ees_bryan_testing_v15 and it will open that table, etc.

My initial plan was to insert all of the data in that table (updated or not) to a temporary/staging table in the database, delete all contents of the target table (ees_bryan_testing_v11 as above) and use a SELECT INTO query to copy all that data over from the temp/staging table and if everything above was successful, clear out the temp/staging table and call it done.

The problem is that I can get one of two things to happen. I can either make all the changes and fire up the process and all it does is insert the original unmodified table (this uses {{targetTableUpdate.normalizedData}} ) to that temp/staging table. Or, when using {{ targetTableUpdate.recordUpdates}} all it will insert to the temp/staging table are those rows that were updated (not surprising, but obviously not the desired outcome).

Any suggestions either in Retool or in the database? BTW, that ees_bryan_testing_v11 table was set up with a question_id field and a question_type field and so while I’d love to just use question_id as the identifier, problem is I might have a combination of 5+Q and 5+D where just choosing 5 as the identifier would update 2 rows.

Thanks in advance!

I'm still interested to hear if there are any suggestions or help on how to accomplish this but in the meantime, I went in and modified all the tables in that schema to include an id column acting as a PK and so now what I'm trying to accomplish above is far easier. Not ideal, but in the long run probably the better solution (for several reasons, not just pertaining to my Retool application)

Hi @therealbmills!

Thanks for reaching out and sharing all of these details!

It sounds like one option is to use Javascript to combine the changeSet with the original data, so that you can work with the full dataset, including pending edits. We have a community post here that describes how to do this.

Generally speaking, most use cases I have seen do include a primary key. Our new table component even has a field where you can define the primary key on the Retool side. If you can move forward with implementing primary keys on your tables, I do think it'll ultimately be easier to work with in Retool.

1 Like