Following the article regarding how to prepare SQL SELECT, INSERT INTO, DELETE query using OpenRefine, we will now see how the template function can be used to prepare UPDATE WHERE statement.
UPDATE WHERE statement are slightly different from a select or an insert statement since we want to define a different where clause for each record. Thus we cannot wrap all rows we want to update into a single query. We will need to write a separate statement for each record in our OpenRefine project and define the SET and WHERE conditions.
In this case, we will use only the Row Template section and leave all other section blank (ie the Prefix, Row Seperator and Suffix sections). OpenRefine Templating Export will iterate through each row of your project and populate the query with the matching set of value.
Keep in mind that the preview display only the 20 first row. You will need to export and rename your file .sql before running the query back on your database!
Below an example to update the Postal Code for an addresse based on it idLocation:
UPDATE WHERE statement are slightly different from a select or an insert statement since we want to define a different where clause for each record. Thus we cannot wrap all rows we want to update into a single query. We will need to write a separate statement for each record in our OpenRefine project and define the SET and WHERE conditions.
In this case, we will use only the Row Template section and leave all other section blank (ie the Prefix, Row Seperator and Suffix sections). OpenRefine Templating Export will iterate through each row of your project and populate the query with the matching set of value.
Keep in mind that the preview display only the 20 first row. You will need to export and rename your file .sql before running the query back on your database!
Below an example to update the Postal Code for an addresse based on it idLocation: