Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Allowing unchanged values in mySQL update PreparedStatement

 
Fabio Piergentili
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am using

update table set x=?, y=?, z=? where id=?;

I do not want to have to vary the number of variables as the code gets messy. How can I designate a value that is not to be changed?

In mySQL, the command update table col=col; keeps col unchanged but if I use setString, PreparedStatement supplies update table col='col' which changes the value or gives exception if other then string. Is it possible to do what I am trying to do?

Thanks for any help.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34095
337
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Fabio,
If you leave out the column from the set list, the database leaves it alone. For example, the following just updates the "x" column and leaves the others alone.

 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34095
337
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Fabio Piergentili wrote:I do not want to have to vary the number of variables as the code gets messy.

Oh. I missed this on first read. You'll have to vary the number of variables. There isn't any binding variable you can pass to the prepared statement that does what you want.
 
Fabio Piergentili
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you.

I just pass it the "variable" anyway. I believe that MySQL will not update it if it is the same value as the current value.

From dev.mysql.com

"If you set a column to the value it currently has, MySQL notices this and does not update it."

Fabio
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic