Issue
The simplest way, requiring minimal input, to change a value in a specific field, en-masse, is to use a Structured Query Language (SQL) statement to apply the change. This can include a filter to ensure only data of a specific type is amended, such as within a date range.
Solution
In the example here, we are showing how the Local Authority field cam be populated. The assumption is that perhaps the data hasn’t been added for this field as it is always the same. However, when the data is used across Authorities, or by the DfT, it is often helpful to be able to see and search on the local authority field.
The SQL statement is as follows:
UPDATE accident SET localAuthority = ‘E07000222’ where accDate between '01-Jan-2018' and '31-Dec-2018'
Note how this breaks down. The first part calls the ‘update’ method, which is not strictly a query as per the ‘Q’ of SQL. This provides for data to be modified, not just selected. The second part specifies which table the field to be modified is held, in this case the main attendant circumstances tables – ‘accident’. The second part specifies the filed to be changed and the value, a string which is the ONS LA code, that the field is to be set for the records in the table. Lastly, a filter is applied to ensure that only records between the dates specified (‘where’ the date of records matches, or lies between, the specified dates). Other filters can also be applied as required.
To apply this statement to the data, you click to access the ‘SQL Search’ screen as shown below. The screen has an area (coloured) grey for typing the statement, or this can be pasted in if you have it already from an email for example. Note also that this can sometimes be supplied in a file, that has an .sql file extension and this can directly be loaded in from the file.
In the image below the statement has already been copied (pasted) in from the clipboard.
Tip - Whilst the above is a very simple example, SQL is incredibly powerful. The ‘Search Database’ screen in KeyACCIDENT provides a simple means for creating quite complex queries, however, its simplicity can sometimes limit what can be done. Below is an example of a more complex search query:
select a.refAttend, v1.ref as Veh1Ref, v1.type as Veh1Type, v1.impact as Impact, v2.ref as Veh2Ref, v2.type as Veh2Type, v2.impact as Impact from accident a inner join vehicle v1 on v1.accidentId = a.id inner join vehicle v2 on v2.accidentId = a.id where v1.impact = 1 and v2.impact = 2 and a.accDate between '01-Jan-2018' and '31-Dec-2018' order by a.accDate
Yes! It is complicated. This query is a search for collisions involving shunts which is needed since in 2011 the data for the part of the vehicle first hit, is no longer collected.