sql script

#11

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

Quote:

Brian (b.houghtby (AT) eaglecrusher (DOT) 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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/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.

#12

Brian (b.houghtby (AT) eaglecrusher (DOT) com) writes:

Quote:

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.

So are there are any rows in GL_TransLine with values in the column
GLL_ItemID starting with 99l?

Could you post the CREATE TABLE statements for the two tables, including
the definition of primary and foreign keys?

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

sql script

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Re: sql script - 04-27-2007 , 09:28 AM

Re: sql script - 04-27-2007 , 04:43 PM