Bulk Change Customer Price Level


C

Chris

We recently instituted a customer loyalty program. I need to set the price
level for the 900+ custoemrs with these new cards and account numbers to
Price level A. Account numbers are sequential and I can query them in
reports but I lack the SQL know how to make the change. Any ideas. Starting
Account number is 101010 and ending is 1010955
 
Ad

Advertisements

C

convoluted

Hi Chris

Try....
update customer
set pricelevel = 1
where accountnumber between 0101010 and 1010955;

I beleive account numbers are 7 digits;

To roll back to all customers having "standard "price level
use
update customer
set pricelevel = 0

Hope this helps....
 
C

Chris

I assume you instructions are for a SQL Query to be run in the
administrator. I tried but got a syntax error. Am I missing something?
 
C

Chris

A new twist to the issue. We have several types of numbering for account
numbers including alphanumeric, and phone numbers for some accounts. Running
the query suggested I get the error
<<An error occurred while executing query:

Syntax error converting the nvarchar value '++Walk In Customer' to a column
of data type int.>>

Any suggestions to get around this?
 
G

Glenn Adams [MVP - Retail Mgmt]

ALWAYS, ALWAYS, ALWAYS BACK UP YOUR DB BEFORE RUNNING Update, Insert or
Delete QUERIES!!!

accountnumber is a string, not a numeric field - put single quotes
around the values...

where accountnumber between '0101010' and '1010955'

Note that '0101010' IS NOT the same as '101010' in a character field -
the leading zero really does make a difference in this case.

You might want to run a select statement using the same where clause
before running the update just to verify that you are getting only the
records you want...

select * from customer
where accountnumber between '0101010' and '1010955'

--or to get just the count of the records,

select count(*) from customer
where accountnumber between '0101010' and '1010955'
 
Ad

Advertisements

C

Chris

Glenn - Thanks that did the trick.


Glenn Adams said:
ALWAYS, ALWAYS, ALWAYS BACK UP YOUR DB BEFORE RUNNING Update, Insert or
Delete QUERIES!!!

accountnumber is a string, not a numeric field - put single quotes around
the values...

where accountnumber between '0101010' and '1010955'

Note that '0101010' IS NOT the same as '101010' in a character field - the
leading zero really does make a difference in this case.

You might want to run a select statement using the same where clause
before running the update just to verify that you are getting only the
records you want...

select * from customer
where accountnumber between '0101010' and '1010955'

--or to get just the count of the records,

select count(*) from customer
where accountnumber between '0101010' and '1010955'
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top