I have managed to get this working for a single item but the bulk update is causing me some issues. The problem is that the text in the Key column is not the same as the name of the column in the DB, so I need some way of mapping the value in the column to the column headings in the DB.
You can change your column name in the table to banana and it would still be able to update the database correctly. Here's an example of a mySQL bulk update query and how you could set it up. The red shows the correct column name despite being name different on the table:
Then, you'll use .recordUpdates (purple) to get the rows that have been changed in your table. And finally, you'll add an event handler (green) on the table that triggers this update query when you save changes:
Thanks for the input! Unfortunately, in my use case, it's not the column name that relates to the DB field but the value in the column., think of it like a DB table displayed vertically rather than horizontally, so the DB field names are displayed down the left-hand side of the table rather than across the top row. So in the image, I attached I have a column value that says:
In the DB, that field name is:
The value I want to be able to change in that field is AMD.
So using my image as an example the update SQL statement might look like:
UPDATE components SET (what_cpu = 'Intel', qty_of_ram = '128', what_gpu = 'AMD') WHERE....;
Hopefully, that makes the use case a little clearer.
Having just thought about it writing this out, perhaps there is a way of displaying a SQL table vertically instead of horizontally in Retool?
I think that would work great, so thanks very much for sharing this solution.
In order to progress my project forward, I changed the UI in order to use these values in a normal horizontal table. I am only working on a PoC at the moment so if the UI needs fixing when it gets moved into production it can be done then.