Bulk update multiple columns

name_is_too_short · June 25, 2023, 2:33pm

I have a SQL table called 'orders' with the columns: 'id', 'name', 'type', 'price', and 'allow'. Let's say we have the following data in the 'orders' table:

In this table, 'id' is the primary key (an incremental number), 'name' and 'type' are text, 'price' is a number, and 'allow' is a boolean.

We have several types of elements in this table. Now, let's assume we want to "allow" them as a group based on the 'name' column rather than individually because it would be time-consuming.

To achieve this, I use the following query:

SELECT name, allow, SUM(price) AS total_price
FROM orders
GROUP BY name, allow;

The result of this query, grouped by 'name', and 'allow', would be as follows:

Now, what I do is create a retool table called 'allow' and provide the user with the ability to edit the 'allow' column using checkboxes.

I make the 'allow' column editable and then attempt to perform a bulk update. This is where it becomes complicated because I typically do it in GUI mode using "Bulk update via a primary key" with an array like "{{allow.changesetArray}}".

However, since I used the GROUP BY function, I lost the primary key, and I am unsure how to resolve this issue. I attempted to switch from GUI mode to SQL and tried something like this:

UPDATE orders SET allow = CASE WHEN ({{allow.selectRow.name}} = orders.name) THEN {{allow.selectRow.allow}} ELSE false END;

I am using "selectRow" because I set the "Event handlers" as "Change cell". Unfortunately, {{allow.selectRow.allow}} always gives me false, even if the checkbox is true.

The result I want to obtain, in the table orders, is the following

Could you please help me with this?
I found I similar topic but I could not make it work
Thanks

name_is_too_short · June 26, 2023, 3:44pm

Ok, I have figured out how to do it.

In GUI mode, there is an option that allows you to modify multiple rows at once. Additionally, the table where you edit the data needs to have a primary key. If you use a form to accomplish this (instead of editing the table), it becomes easier. Then, you need to use the "Filter by" function to find the rows you want to edit and apply the changes using "Changeset"-"Key value pairs" with the changesetArray. Dealing with this part using a form makes it much easier.