Problem Updating a table using CASE

[phark]

I am trying to update a SQL table. Basically, I want to take our customers and move some of their invoices to a different batches. The problem seems to be one account that has a non-numeric account number called CASH. All the other accounts are numeric. Here what I tried to run:

update rm20101
    set bachnumb = CASE
	when custnmbr > 9999 and custnmbr < 11000
		then 'BULK-1'
	when custnmbr > 10999 and custnmbr < 12000
		then 'BULK-2'
	when custnmbr > 11999 and custnmbr < 13000
		then 'BULK-3'
	when custnmbr > 12999 and custnmbr < 14000
		then 'BULK-4'
	when custnmbr > 13999 and custnmbr < 15000
		then 'BULK-5'
	when custnmbr > 40999 and custnmbr < 51000
		then 'BULK-6'
	  ELSE 'BULK'
      End;

I get the following error:

Server: Msg 245, Level 16, State 1, Line 2
Syntax error converting the varchar value 'CASH           ' to a column of data type int.

Any suggestions?

Thanks,
Pat

 

[forecasting]

CASE WHEN custnmbr = 'CASH ' THEN 51000 ELSE custnumber END

A kind of ugly solution since it means substituting this case statement for every time you use custnumber

 

[SQLBill]

It looks like the original column is a VARCHAR (that's why CASH is allowed), but you are trying to move the information to something that only allows INT. Check rm20101 and make sure it's set to allow VARCHAR.

-SQLBill

 

[phark]

What I am trying to do, is change data in certain rows in the table RM20101. Most of the data in the BACHNMBR column currently has the value of BULK.

I want to change all values in the column BACHNMBR to a different value, based on their customer number (CUSTNMBR).

Example: Customer number 13257 has the vaule BULK in the BACHNMBR column. I want this changed from BULK to BULK-4, because this customer number is between 12999 and 14000.

This seems like it should be easy to do... not sure if what I wrote to do this is even in the right direction, as I am newbie to SQL.

Thanks for all the help!

 

[phark]

Here is what ended up working, FYI:

update rm20101
    set bachnumb = (CASE
	when custnmbr > '9999' and custnmbr < '11000'
		then 'BULK-1'
	when custnmbr > '10999' and custnmbr < '12000'
		then 'BULK-2'
	when custnmbr > '11999' and custnmbr < '13000'
		then 'BULK-3'
	when custnmbr > '12999' and custnmbr < '14000'
		then 'BULK-4'
	when custnmbr > '13999' and custnmbr < '15000'
		then 'BULK-5'
	when custnmbr > '40999' and custnmbr < '51000'
		then 'BULK-6'
	  ELSE 'BULK'
      End) [\code]