Posted by Brian on 04/27/07 14:28

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns991EF17EC77BCYazorman@127.0.0.1...
> Brian (b.houghtby@eaglecrusher.com) writes:
>> Sorry, Let me start over. I am trying to do a mass change of a bunch of
>> numbers that a user entered incorrectly. He entered them with a lower
>> case letter instead of uppercase. The numbers all begin with a 99l and
>> I want to do a mass replace to change them all to a 99L. The numbers
>> have a suffix that is not constant like the prefix. Example; 99l555,
>> 99l556, 99l557 and so on.
>>
>> The name of the table is dbo.item and the column is ima_itemid. Here is
>> my select statement.
>>
>> Select * from dbo.item where
>> ima_itemid like '99l%'
>>
>> This result gives me a mixture of 99l's and 99L's. So apparently the
>> user eventually started entering them the correct way, in case that is
>> an issue having a mixture. It appears that my select statement doesn't
>> care about case since I get upper and lower, although I used lower in my
>> select. Hope this clarifies some.
>
> Then this should do it:
>
> UPDATE item
> SET ima_itemid = replace(ima_itemid, 'l', 'L')
> WHERE ima_itemid COLLATE Latin1_General_BIN LIKE '99l%'
>
> By foring a binary collation, only rows with the incorrect pattern
> are selected. This should not trigger an FK constraint violation,
> given what you have said about both 99l and 99L being returned.
>
> The suggestion from Andrey to add a constraint to prevent this from
> happening again is an excellent idea you should pursue.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

I get this error when I run it.

Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the REFERENCE constraint
"FK_GL_TransLine_IMA". The conflict occurred in database "iERP80_EAGLE",
table "dbo.GL_TransLine", column 'GLL_ItemID'.
The statement has been terminated.

Strange thing .... If I change the 99l% to 99L% it updates 5 rows, with the
lowercase l, I get the error.

[Back to original message]