Update database after retrieval

{{container}}{{container layoutStyle="columns"}}(((

In our previous [[microlearning>>doc:Main.eMagiz Academy.Microlearnings.Intermediate Level.Database connectivity.intermediate-database-connectivity-sql-query||target="blank"]], 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.

4

5

Should you have any questions, please get in touch with [[academy@emagiz.com>>mailto:academy@emagiz.com]].

6

7

== 1. Prerequisites ==

8

9

* Basic knowledge of the eMagiz platform

10

11

== 2. Key concepts ==

12

13

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.

14

15

== 3. Update database after retrieval ==

16

17

In a previous [[microlearning>>doc:Main.eMagiz Academy.Microlearnings.Intermediate Level.Database connectivity.intermediate-database-connectivity-sql-query||target="blank"]] 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.

18

19

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 {{code language="sql"}}UPDATE mytable SET changeddate = :headers[timestamp] where id = :headers[id]{{/code}}. However, with this functionality, the update statement should look like this {{code language="sql"}}UPDATE mytable SET processed = TRUE where id IN (:id){{/code}}.

20

21

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.

22

23

[[image:Main.Images.Microlearning.WebHome@intermediate-database-connectivity-update-database-after-retrieval--select-and-update-combined.png]]

24

25

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.

26

27

== 4. Key takeaways ==

28

29

* eMagiz allows you to execute an update statement immediately after a select statement.

30

* This feature is particularly useful for tracking and updating record statuses, preventing repeated retrieval of the same records.

31

* The update statement applies to all rows returned by the select query, with the changes based on the specified conditions in the where clause.

32

33

== 5. Suggested additional readings ==

34

35

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:

36

* [[Intermediate Level (Menu)>>doc:Main.eMagiz Academy.Microlearnings.Intermediate Level.WebHome||target="blank"]]

37

** [[Solution Architecture (Navigation)>>doc:Main.eMagiz Academy.Microlearnings.Intermediate Level.Database connectivity.WebHome||target="blank"]]

38

*** [[SQL Query (Explanation)>>doc:Main.eMagiz Academy.Microlearnings.Intermediate Level.Database connectivity.intermediate-database-connectivity-sql-query||target="blank"]]

39

* [[Update database (Search Results)>>url:https://docs.emagiz.com/bin/view/Main/Search?sort=score&sortOrder=desc&highlight=true&facet=true&r=1&f_space_facet=0%2FMain.&l_space_facet=10&f_type=DOCUMENT&f_locale=en&f_locale=&f_locale=en&text=%22update+database%22||target="blank"]]

40

* [[SQL Explained (External)>>https://www.w3schools.com/sql/default.Asp||target="blank"]]

41

42

43

)))((({{toc/}}))){{/container}}{{/container}}

Wiki source code of Update database after retrieval

Navigation