• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

SQL: Want to update multiple rows in a single update command, but roll back if any of the rows doesn't exist.

Let's say i want to do 3 updates in a single update statement.
 update table set columnB = 'abcd' where columnA in ('1','2','3')
Assume columnA='1' exists but not for '2' or '3'.
The second update is not valid(the key doesn't exist in the db). The third update is also not valid.
So i don't want any update to happen. I tried doing this but it didn't work:

connection.setAutoCommit(false);
"update statement to update 3 rows, 2 of which don't exist."
connection.commit();
0
renisenbb
Asked:
renisenbb
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if connection returns the number of rows affected (if not, you can query it:
https://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_row-count)

and if that does not match the number of values put in, you rollback, otherwise you commit

note: rollback will not work on MyISAM tables
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now