CODEWITHSUNDEEP
javasqljdbcdb2
Sabir Khan
Sabir Khan

Reputation: 10132

Get details about SQL updated records

I need suggestions about a logic. There is an update query in application like below 

UPDATE TABLE 
   SET FLAG = CASE 
                WHEN FLAG = 'IP' THEN 'P' 
                WHEN FLAG = 'IH' THEN 'H' 
                WHEN FLAG = 'IM' THEN 'M' 
              END 
WHERE ADJUSTMENT_ID IN (SELECT Query )

This update is executed from a Java function which returns void.

Now I have a requirement to get details of updated records too (few columns from table TABLE) and return a LIST from function instead of void.

Running SELECT first then updating records in loop is not an option due to performance reasons. Records are updated with a single UPDATE statement because its supposed to run faster.

What would be options for me keeping comparable performance? Should I go with a stored procedure?

Upvotes: 0

Views: 207

Answers (1)

MichaelTiefenbacher
MichaelTiefenbacher

Reputation: 3995

SELECT ... FROM FINAL TABLE (UPDATE ....)

will do the job. As it is a single SQL statement the performance will aslo be good.

See also http://www.idug.org/p/bl/et/blogid=278&blogaid=422

Upvotes: 4

Related Questions