why are not using any where condition in the update statment On Wed, May 16, 2012 at 1:24 PM, GF <gan...@gmail.com> wrote:
> Good morning, > I have an application where the user ids were stored lowercase. > Some batch import, in the user table some users stored a uppercase > id, and for some applicative logic, in other tables that have a > foreign key to the user table, their user ids are stored lowercase. > MySQL didn't throw any error probalby because the collation used is > "case insensitive". > My problem is that the application is Java and java strings are case > sensitive, so now I want to set user ids to lowercase EVERYWHERE. > > I supposed that I could execute with ease these commands: > - update mytable1 set USER_ID = LOWER(USER_ID); > - update mytable2 set USER_ID = LOWER(USER_ID); > - update mytable3 set USER_ID = LOWER(USER_ID); > > But for some tables I got some Foreign key constraint to throw an > error. (butwhy they didn't throw an error on the insert but just on > the update???) > And if I try to disable foreign key checks during these updates, I get > some "duplicate key" errors where USER_ID is a part of composite key > with other columns. (but I don't have any data that might cause a real > duplicate key error just changing the case of one column) > > Have you any idea how to solve this situation without > stopping/recreating the DB? (it's a production environment) > Thanks > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql > >
