This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes Allowing unchanged values in mySQL update PreparedStatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Allowing unchanged values in mySQL update PreparedStatement" Watch "Allowing unchanged values in mySQL update PreparedStatement" New topic
Author

Allowing unchanged values in mySQL update PreparedStatement

Fabio Piergentili
Ranch Hand

Joined: Sep 04, 2009
Posts: 57
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30130
    
150

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.



[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30130
    
150

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

Joined: Sep 04, 2009
Posts: 57
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
 
Don't get me started about those stupid light bulbs.
 
subject: Allowing unchanged values in mySQL update PreparedStatement
 
Similar Threads
selective column update
Update Table Nested Query (Using Function)
Can anyone use IN clause of query with preparedStatement like below?
Displaying image from database
prepared statement to update table with hundreds of columns