What is the difference between Result Group and Record Set in Database Update section of a source node?

Question

I'm working on finding a reliable way to mark records in a SQL database as being processed after the select query but before they're all completed so if the query runs again before the messages are done processing they aren't added as messages again. I understand the "Row" option, but I don't know what the difference is between Record Group and Result Set... I would have thought those options were synonymous. The reference manual doesn't say much about them. What are the differences?

Comments

Maybe it would be good to know for sure what "Row" means too. If I did something like "SELECT TOP 11 id, tiProcessed FROM MyTable" and grouped by Row Count, and I wanted those very same 11 records to be updated like "UPDATE MyTable SET tiProcessed = 1" could I do that by adding a WHERE clause with a node tag like "WHERE id = {id}" and then execute every Row?

Answer 1

The 3 options are:

1) "Result Set" will run the Database Update query only once per result set from the SQL Statement defined in the "Database Query" fieldset.  When the query is executed, it will take the first row, and execute the update statement, then process the rest of the rows from the result set without executing the update statement for each subsequent row.

Example:

Database Query: "SELECT ID, NAME, ADDRESS, PHONE FROM SOME_TABLE WHERE PROCESSED = 0"

Database Update: "UPDATE SOME_TABLE SET PROCESSED = 1 WHERE PROCESSED = 0". 

This would work, but if a record was inserted at just after the SELECT statement was run, then it would get lost because the UPDATE statement would run and mark the new record as processed.

2) "Record Group" will execute the Database Update query once for each group in the "Record Grouping" field set.

Example:

Database Query: "SELECT LOCATION, NAME, ADDRESS, PHONE FROM SOME_TABLE WHERE PROCESSED = 0 ORDER BY LOCATION DESC"

GROUP BY: "Column Value" monitoring the first column

Database Update: "UPDATE SOME_TABLE SET PROCESSED = 1 WHERE LOCATION = "{LOCATION}""

Then all of the records with the same LOCATION would be treated as a single message and sent into the channel as a single message.   Whenever the LOCATION changes, the Database Update statment will be executed.  The node tag {LOCATION} will get replaced with the actual value from the query result.

3) "Row" will execute the Database Update query once for each row in the result set.

Example:

Database Query: "SELECT ID, NAME, ADDRESS, PHONE FROM SOME_TABLE WHERE PROCESSED = 0"

Database Update: "UPDATE SOME_TABLE SET PROCESSED = 1 WHERE ID = {ID}"

For every row in the result set, the update statement will be run replacing the {ID} node tag with the ID value from the result record.

 

Option #3 is the safest approach as it updates the records as they are processed.  However, that means that you have to query a primary key column(s) to make sure to update the specific row for each record