SQL statement to log unupdated element from IN clause

Question

I am updating a table with SQL IN clause with multiple parameters. I would like to know the parameter which could not update. Is there any way I get that info from SQL update statement? I have a PUT request body something like below

{
    "personState": "N",
    "personCodes ": [
        "000418",
        "000478",
        "000058",
        "000190",
        "000217",
        "000284",
        "000344"

    ]
}

The values from the 'personCodes' field are inserted into the 'in' phrase in the HQL query. I need to find those values that were used in the query, but for which no row in the database was updated.

My SQL code snippet something like below

public interface PersonRepo extends JpaRepository<Person, Integer> {

    @Modifying
    @Transactional
    @Query(value = "update person set person_state = ?1, date_updated = GETDATE() "
            + "where person_number = ?2 and person_code in (?3)", nativeQuery = true)
    void updatePerson(String personState, String personNumber, String personCodes []);
}

Answer 1

Unfortunately there is no way to get this information from a single update query. With an update, you may only know the number of rows that have been updated. The JDBC driver does not inform you which specific rows were affected. So you need to find another way of knowing which rows have been updated and which have not. There are several ways of doing that. For example:

1. Identify updated rows post factum

If you can update the row with some value that you know is unique, then you may then select the rows you updated. For example:

update person set person_state = ?1, date_updated = GETDATE() ", identifying_column = ?2
where person_number = ?3 and person_code in (?4)

And in the place of ?2 you may put for example an UUID.

Then you may see what rows have been actually updated:

select person_code from person where identifying_column = ?1

Knowing which rows have been updated and having list of personCodes from request, you may tell which codes have not been affected.

If there are not too many requests than maybe even date_updated may be that unique value. But that would be of course risky.

PS Be sure that you are doing update and select in the same transaction. Otherwise, some other process/thread may overwrite this column before you check.

2. Use batching updates

That would be of course slower but you may run multiple updates in a batch, like this:

update person set person_state = ?1, date_updated = GETDATE()
where person_number = ?2 and person_code = ?3

Every such update would return "0" or "1" - then you know which row has been updated and which has not. But be sure you run those queries in batch. Running them without batching would slower your code significantly.