Updating a SQL column cell based on a SELECT GROUP BY

Hi All,

I am new around here and would like some coding help. I am not a coder and cannot solve this problem I have.

I have two tables. One table lists calibrations (SQL Table name: gaugecalibration). The other lists gauges to which the calibrations relate (SQL Table name: gaugelist). The calibration table may have multiple entries for each gauge.

calibrationsTable.data query:

SELECT id, gaugeid, certno, nextcalibrationdate FROM gaugecalibration

gaugeTable.data query:

SELECT id, gaugeref, calibrationstatus FROM gaugelist

The gauge calibration column gaugeid is linked to the id in the gauge list.

I have a third query below, which filters the next calibration dates to show the gauges which will go out of calibration if the date is before today.


SELECT gaugeid AS "ID", max(nextcalibrationdate) AS "Calibration Date"
FROM gaugecalibration 
WHERE gaugeid IS NOT NULL AND nextcalibrationdate < {{ moment().format('YYYY-MM-DD') }}
GROUP BY gaugeid
ORDER BY gaugeid


To this point, everything is working how I expect it to.
My problem is I would like to create a way to take each gaugeid AS 'ID' from the nextcal_filter query and update the related calibrationstatus cell in the gaugelist to be 'OUT OF CALIBRATION' matching the id.

I tried using the GUI update a row query but received errors. I am guessing I need a way to run the change on the array simultaneously or loop through the array to update, and I am struggling to find that solution. Any help with the coding would be great.

Thanks, Nathan.

Hey Nathan! Happy to help with this. A couple questions to start.

1. What type of update are you currently running? Sounds like a bulk update would be best?

2. What errors are you currently running into?

Hi victoria

At present, I am not running an update. You are correct that I want to take an array of ids from the outofcal_gauges (list shown above). These ids reference the gauge, which is out of calibration.

I would like to bulk update a table (gaugelist) using the ids and set the calibrationstatus. Where the calibrationstatus would be 'OUT OF CALIBRATION'.

My question is, how would I create the bulk update with the ids in the array?

In terms of errors, they are more to do with the fact I'm not sure how to do a bulk update.

The last error I received when trying was that my data must be an array. As shown below.

Just FYI,

I managed to complete the task by using a transfer on the query to convert the data to an array. I could then use the bulk update option as above

let data = {{ outofcal_gauges.data }}
return formatDataAsArray(data)

1 Like