I have a Retool query using the GUI config that updates a single record in a snowflake database.
When I preview the query, the output shows numberOfRecordsThatWouldBeUpdated: 1, however when I run the query I get the error:
statusCode:422
error:"Unprocessable Entity"
message:"Updating multiple records with this query has not been enabled."
My query was working no problem up until yesterday and I didn’t make any changes. The only way I can get it to work now is to check “Allow this query to modify multiple rows in the database?” (what exactly does this do?)
I’ve confirmed there are no duplicates in my database. I think this might be a new Retool bug.
Could you share/export app JSON for me to clone and try to reproduce?
That is so odd the checkbox fixes the issue. I was have guessed that the box would throw an error and turning it off would fix it
Not sure what the box checking is doing under the hood, my guess is that it might be modifying something in the query during query build.
My guess is that the box turns off the error checking, with the bug being in the detection of how many rows are being updated. Which is somehow coming back as a false positive even though the preview confirms the query is set up to only modify one row as intended...
Were you able to get any information on what's happening under the hood when "Allow this query to modify multiple rows in the database?" is checked/unchecked?
So my guess is that there were some engineering attention on this very recently to get things sorted let me know if it still requires a check box to update a single row!
I haven't seen any other resource queries with this option, so I am guessing the functionality is unique to snowflake being able to potentially update way too much/all data and needing more under the hood query transformation by the Database Connector.
Are you self hosted/what version of Retool are you using? In the GIF you shared I did not see the modal pop up that I had when toggling the check box so I still have hope that updating things might fix the error message.
My guess for why the error is popping up is that there are multiple rows with the same ID (in your example ID 1). But you did run the query preview and it said you were only modifying one entry.....
Is there any other issues caused by leaving this checked? I tried to reproduce it on the latest version of Retool and couldn't get the error to show up for me
Hey @Jack_T !
My org is using Retool Cloud on business plan, so I believe we're always on the latest version of Retool?
I didn't notice that the popup was missing, but I had seen it the day I created that screen recording (as I tested it to see if the issue was fixed, and then I made the recording). Perhaps it just didn't pop up a second time?
We've discovered that we can switch to plain SQL queries for our Updates and avoid having to check that checkbox (which is only visible for GUI queries). So we'll just roll with that for now.
Could you follow up on what checking/unchecking Allow this query to modify multiple rows in the database? does? Thanks for all your help!
Yes that is correct, if you are on Retool Cloud you will always ben on the latest version
Interesting that the popup did appear for you, maybe it turns off after a certain number of toggles?
I am so happy you found a work around! I didn't know Snowflake could take plain SQL queries but that is really good to know. We are planning an overhaul of our Snowflake GUI!
Can follow up on the purpose of the checking toggle. I imagine that in our database connector it might be a param that Snowflake is looking for, but maybe it is for our internal query builder to better create raw SQL that fits best with what Snowflake is expecting
If you like this solution, I can help you out with stuff like this! I run Tropic Flare consulting (https://tropicflare.com) where I help people develop and debug Retool apps like this.
let me know if it still requires a check box to update a single row!
