SQL – CASE WHEN Update

By David Crowther

Question:

How do you use the values in one field to auto update values in a different field?

Answer:

Using the DynamicMaps webGIS – MapThat – we have users editing a Planning Apps Layer choosing to insert/update a value of either Passed, Submitted or Declined into the Progress field.

Once the user presses Save the changes are committed…

…with the new record inserted into the source database table, with the value Passed in the Progress field.

However, you may wish another field to be used to store the CODE for the Progress. So here we have updated the DESIGN of the table to include an extra field called Progress2.

To automatically update this new Code field (Progess2) we can apply a TRIGGER to the Database table. The Trigger will run on Insert and Updates into the table and update the Progress2 field with a value based on the user’s choice for the Progress field.

• Passed = P
• Submitted = S
• Declined = D

Here are the Trigger details for my table and field names.

Once ran the new Trigger is created it is attached to the table.

If we now make a change to the Planning Apps table, for example using the webGIS MapThat, once the user creates a new record and chooses a value for the Progress field e.g. Submitted,..

… the new record will be insrted into the source table, and the trigger will auto update the Progress2 field with the value of ‘S’ based on the triggers CASE WHEN statement -

Triggers are a great way to auto update fields in your source tables. And in this case, it is a great way to allow the user to choose a Well-Known Value e.g.. ‘Submitted’ from a list, but then update your source table with the actual CODE required ‘S’.