Get id of rows that were updated on request

Question

I want to get the primary key column value for rows affected by a Java request. I am using MySql DB. Suppose the query is similar,

update user set pincode = 390023 where area like '%ABC Road%' 

Then in java I want to update the identifiers of each row (the primary key of each row). Something that might be possible with a Statement object in Java maybe.

Answer 1

Select the rows just before updating them. Use select id from user where area like '%ABC Road%' .

Remember to include these two operations in the transaction to avoid row changes between the two.

Answer 2

 SELECT id FROM user WHERE area LIKE '%ABC Road%'; 

or alternative way:

 SET @ids = NULL; UPDATE user SET pincode = 390023 WHERE area LIKE '%ABC Road%' AND (SELECT @ids := CONCAT_WS(',', id, @ids)); SELECT @ids;