Update database after retrieval
In our previous microlearning, we explored the basics of SQL queries. Now, we will take it a step further by learning how to update database records immediately after retrieving them. This approach is useful for managing statuses, ensuring you do not repeatedly read, or retrieve, the same records. Let's dive into how this functionality works and how to implement it within eMagiz.
Should you have any questions, please get in touch with academy@emagiz.com.
1. Prerequisites
- Basic knowledge of the eMagiz platform
2. Key concepts
eMagiz offers the option to execute an update statement right after a select statement. In the database, this can be useful when tracking the status.
3. Update database after retrieval
In a previous microlearning we discussed the fundamentals of SQL queries. In this microlearning, we will learn how you could update the record(s) in the database the moment after you have read the information from the database. This functionality can help you update a status field to ensure you won't keep reading the same record(s) repeatedly.
To update the records we just read from the table, we need a slightly different update statement. As you probably remember the update statement in the previous example was written as follows UPDATE mytable SET changeddate = :headers[timestamp] where id = :headers[id]. However, with this functionality, the update statement should look like this UPDATE mytable SET processed = TRUE where id IN (:id).
As you can see, there are some subtle differences in the SQL query you need to achieve the proper result—most notable, the where statement is written differently. In the update statement, we need here; we need to define our input (IN) columns and values. Based on the input values that were returned via our SELECT statement, the update action is triggered. Within eMagiz, this is configured as follows.
In this example, we search for all rows within the table for which the value in the column processed is equal to false. If so, the select statement will return each row. After the query returns all rows, the update query will ensure that all these rows will be updated by changing the value in the column processed to true. This way, you ensure these records won't be retrieved the next time we execute the select statement.
4. Key takeaways
- eMagiz allows you to execute an update statement immediately after a select statement.
- This feature is particularly useful for tracking and updating record statuses, preventing repeated retrieval of the same records.
- The update statement applies to all rows returned by the select query, with the changes based on the specified conditions in the where clause.
5. Suggested additional readings
If you are interested in this topic and want more information, please read the help text provided by eMagiz and read more information on the following link: