This guide is a faster way to do bulk updates then the JS promise method prescribed by retool.
the process takes a set of arrays with the update data, converts those arrays to JSON, and then passes the JSON to a query that will execute all the updates at once.
Prerequisites:
- You must have all your update data formatted into mapped arrays of values:
- [client_id1, client_id2]
- [contract_id1, contract_id2]
- [amount1, amount2]
- etc…
- Pass these arrays into a javascript query to format into a set of JSON objects using the following code:
const data = cccl_output_query.data; // pull in your data (arrays).
const c_id = data.campaign_id; //extract array from data.
const state_code = data.state_code; //extract array from data.
let temp_array = []; //empty array to hold JSON.
//pass the values into the below code as "column_name" : "${array_value[i]}" separated by commas.
for(i=0;i<c_id.length;i++){
temp_array.push(`{
"campaign_id" : "${c_id[i]}", //input your 1st array value starting here.
"state_code" : "${state_code[i]}" // input 2nd array value here.
}`
);
}
let json = '[' + temp_array.join(',\n') + ']' //joins and completes the JSON code.
return {json: json} // return JSON code to pull into your database query.
MSSQL:
Create the update query using the code below:
--follow steps 1-7 below to update this query for your own uses.
--All variables, column names, ect... must appear in the EXACT SAME ORDER.
declare @campaign_id INTEGER;
declare @state_code varchar(255);
--step 1: (above) declare all the variables exactly as the names exist in the database. declare @col_name [col_type]
declare cur CURSOR FOR
SELECT * FROM OpenJson({{query.data.json}}) --step 2: update this to point towards your JSON query output.
WITH (campaign_id INT '$.campaign_id',
state_code VARCHAR(100) '$.state_code'
)
--STEP 3: update the (above) values to match your data/col_names: col_name [col_type] '$.col_name' repeat for as many columns as you need to update.
OPEN cur;
FETCH NEXT FROM cur INTO @campaign_id, @state_code --STEP 4: input all your @col_names here separated by commas.
WHILE @@FETCH_STATUS = 0
BEGIN
insert into table
(campaign_id, state_code, created_at, updated_at)
values (@campaign_id, @state_code, current_timestamp, current_timestamp)
--STEP 5: replace the insert query above with your insert/update query
--STEP 6: replace the values(value1, value2) in the query with to your variables declared at the top of the code: values(@col_name1, @col_name2, @etc...)
FETCH NEXT FROM cur INTO @campaign_id, @state_code --STEP 7: input all your @col_names here separated by commas.
END;
CLOSE cur;
DEALLOCATE cur;
POSTGRES:
- Create update query using the code below:
--follow steps 1-3 below to update this query for your own uses.
INSERT INTO account_payment_schedules
(contract_id, account_id, amount, finance_fee, deleted, schedule_date, status, created_at, updated_at, payment_number, original_schedule_date, created_by_person_id)
--STEP 1: (Above) paste your update query to replace the one above.
SELECT "data".*
FROM generate_series(1,1) i
CROSS JOIN LATERAL (
--STEP 2: in the (below) select query, you need to input all the values from your query in the EXACT SAME ORDER.
--lines that start with (j.items->>) must be updated with your columns names and cast to the correct data type.
--(j.items->>'col_1')::data_type
--values that do not appear in the JSON, such as schedule_date in this example can be hard coded using "value" as col_name. but still appear in the EXACT SAME ORDER as your update/insert query.
SELECT (j.items->>'contract_id')::int as contract_id,
(j.items->>'account_id')::int as account_id,
(j.items->>'amount')::NUMERIC as amount,
(j.items->>'finance_fee')::NUMERIC as finance_fee,
false::BOOLEAN as deleted,
CURRENT_DATE as schedule_date,
'scheduled' as status,
current_timestamp as created_at,
current_timestamp as updated_at,
(j.items->>'payment_number')::INTEGER as payment_number,
CURRENT_DATE as original_schedule_date,
{{sel_mod_user.data.person_id[0]}}::INTEGER as created_by_person_id
FROM json_array_elements({{query.data.json}}::json) j(items)
) "data";
--STEP 3: in the FROM statement above, update the javascript variable to point towards you JSON query output. the code between the {{}}`