Arithmetic overflow error

[NewbieDBA]

Hi Folks,

The idea behind the query is to get every address_line_1 that has 'Street' and use notepad to make them all 'St' instead. Inane, but someone must have it this way.

I can't get the following to run:

select 'update address set address_1 = ~' +address_1+ '~ where address_id = ~' + address_id + '~'
from address
where address_1 like '%Street%'

I get this error message:
Server: Msg 8115, Level 16, State 6, Line 1
Arithmetic overflow error converting varchar to data type numeric.

The address_id is a numeric column and address_1 a varchar. This same query works with the customer column instead of the address_id, but this is impractical because customers may have many addresses.

Advice?

 

[fluteplr]

Your query looks totally wierd to me, however why don't you just do it the easy way.

update address
set address_1 = replace(address_1,'Street','St')

do the following select to make sure it works the way you want it to first.

select address_1, replace(address_1,'Street','St')
where address_1 like '%street%'

 

[NewbieDBA]

fluteplr
I've never really seen this structure before, and it works great. Thanks

Let me show you what my structure does.


Select 'update address set address_1 = ~' +address_1+ '~ where customer = ~' + customer + '~'
FROM address
WHERE address_1 LIKE '%Street%'

Results
--------------------------------------
update address set address_1 = ~141 Cambridge Street~ where address_id = ~00163984~
update address set address_1 = ~141 Cambridge Street~ where address_id = ~00163983~
update address set address_1 = ~521 Wall Street~ where address_id = ~00163996~
update address set address_1 = ~720 Fourth Street N~ where address_id = ~00162028~

I alter data in MS NotePad with find/replace.
(~) becomes (')
(Street) becomes (St)

Now these results get pasted back into QA to run.


update address set address_1 = '141 Cambridge St' where address_id = '00163984'
update address set address_1 = '141 Cambridge St' where address_id = '00163983'
update address set address_1 = '521 Wall St' where address_id = '00163996'
update address set address_1 = '720 Fourth St N' where address_id = '00162028'

Not as useful in this instance as yours. But it has some great applications under certain circumstances.


This is great for security grants.

select 'grant execute on' + name+ 'to' + 'UserNAME'
from sysobjects
where type = 'P'
ORDER BY NAME

grant execute on web_mtg100_sp to UserName
grant execute on web_ord710_sp to UserName
grant execute on web_pcc100_sp to UserName
grant execute on web_sub710_sp to UserName

Simply cut and paste from lower window in QA to upper window and all those _sp permissions are set.

Cheers
Adam

 

[fluteplr]

Got it, I have done the same thing I think it was the ~ that confused me.

You can save yourself a step by doing the following.

Instead of the ~ put in char(39).

You can also do most search and replaces right in QA.

 

[tlbroadbent]

Omit the ~ and ' around the address_id number. That is what cauases the error. You are comparing an int column to a character string.

update address set address_1 = '720 Fourth St N' where address_id = 00162028 Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.