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!