SQL Update using multiple WHERE conditions

I have a SQL 2008 R2 database with 2 fields in it, MaterialNo and SalesPerson. Lets say the database has 5,000 records in it. I was just informed via an Excel spreadsheet that 300 of these 5,000 material numbers have the wrong SalesPerson assigned. The spreadsheet lists all the 300 material numbers and the correct SalesPerson. Normally I would just use a simple

UPDATE table
SET SalesPerson='John Doe'
 WHERE MaterialNo='12345'

But I do not want to do 300 numbers one at a time like this. What would the process or syntax be to do something like this:

UPDATE table
SET SalesPerson='John Doe'
 WHERE MaterialNo='MY LIST OF 300 MATERIAL NUMBERS'

I am a SQL novice so don't assume anything :-)
thx experts...
Bob R
bobrossi56Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
It's not ideal (not assuming, so don't shoot the messanger) to have a WHERE MatierialNo NOT IN (a list of 300 numbers), so a couple of ways to handle this..

1.

Is there a way you can construct a WHERE clause that contains these 300, without including any others?  A common date range, currently assigned to some other person, etc.  If yes, use that.  If no ...

2.

Import the list from Excel to SQL Server using Database, right-click > Tasks > Import Data, and follow the prompts to import into a table in the same server.  Assuming a column name new_column_name and new_table_name....

3.

Then execute T-SQL that goes something like this:
UPDATE table
SET SalesPerson='John Doe'
WHERE MaterialNo IN (SELECT new_column_name FROM new_table_name)

Open in new window

0
Leo TorresSQL DeveloperCommented:
The key is you have to use an alias to update table

Update pp
Set [Plan ID] =  SomeValue
from UpdateTable pp
	Join table1 MEME (nolock)
		on MEME.col = pp.[col]
	join table2 e (nolock)
		on e.col = MEME.col
		and pp.SomeDate BETWEEN e.startDate AND e.EndDate
		and e.FLAG = 'Y'
		and MEME.Line = '35'
		and pp.[Plan ID] is NULL

Open in new window

0
UnifiedISCommented:
Can you turn your excel list into a comma separated list of materialno?
Copy the column of values and then paste special into another sheet with the transpose option
then save as csv, open in notepad and copy the long string

Then your query can use your update query like this for the comma-separated list
UPDATE table
SET SalesPerson='John Doe'
 WHERE MaterialNo IN (your long string of material no)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott PletcherSenior DBACommented:
In summary:
1) Import the spreadsheet into a table into the same SQL Server database.
2) Update the main table based on the imported table.

In more detail:
1A) Change the "Format Cells..." of both columns to text to insure a smooth load.  
1B) In SSMS, right-click on the db, go to "Tasks", then to "Import Data ...".  Set the "Data source:" at the top of the screen to "Microsoft Excel", then "Browse..." to the spreadsheet and select it.  Choose the matching "Excel version:" and click "Next >".
1C) Click "Next >" again on the Destination Server screen.
1D) Select "Copy data from one...".  Click Next.
1E) Click the appropriate sheet and type in a temporary table name on the right to import into, for example, import_spreadsheet.  Click Next until you get to Finish, then click Finish.  Hopefully the spreadsheet is now loaded into SQL Server.  The hard part's over!

2) Run the SQL UPDATE:
UPDATE t
SET SalesPerson = i_s.SalesPerson --or whatever that column name is in the imported data
FROM table t
INNER JOIN import_spreadsheet i_s ON
    i_s.MaterialNo --or whatever that column name is in the imported data
    = t.MaterialNo
0
sarabhaiCommented:
You can also construct the UPDATE statement using the excel and then execute these statement in SSMS.
to construct the statement in excel using the concatenation function and make sure your all 300 no. of statement are ready to execute.
Then copy it to SSMS and ran it
0
UnifiedISCommented:
Attached is an example of the method sarbhai described.
For a "one off" update such as this, simplicity trumps performance so go with whichever option is easiest for you to follow.
0
bobrossi56Author Commented:
I used this solution as it was the easiest to accomplish my task. I quickly turned the excel sheet into a .csv and copy/pasted the string into SQL and executed it. Very quick and easy to do.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I am a SQL novice so don't assume anything :-)
Looks like you have been given multiple solutions that worked, so if you needed one criteria over others that would translate to accepting a single answer, then next time explicitly state that in your question, as opposed to a token 'I'm a rookie, don't expect me to think much' comment that would exempt you from that.  Just my .02.
0
bobrossi56Author Commented:
How would I know if I was given multiple solutions that worked? I only tried one, it worked, it was simple, and I awarded points based on that. I am not sure what you seem angry about. I said I was a SQL novice because I am.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
EE etiquette is that when you're offered solutions you try them, or at least acknowledge them and give some reason why you didn't wish to go down that road.  Looking at this question, I see six five comments that at first glance look decent (but maybe not complete), and an admission that you only tried the third one in line.

Just to give you fair warning, if experts catch this as a pattern then they are less likely to provide you future solutions.

Glad you got a successful answer though.
0
UnifiedISCommented:
No worries from me, I don't expect anyone to try all solutions if the first one attempted meets the need.
That being said, it's often nice to split points when many experts provide viable options.
0
bobrossi56Author Commented:
Well as a paying customer, my understanding is I ask a question, be as clear as possible, and wait for solutions. I then receive 1 or more possible solutions, I review each solution, select the one that seems the most complete and easiest and give it a try, if it works I award that expert points. I am not going to try every solution I am given, I don't have that time luxury, and if someone lists a complex solution and someone else lists a 1 step solution of course I am going to go with that one.

How about the numerous times I've ask a question and I get a convoluted answer, so I reply back asking for clarification and nobody ever revisits that question again, so I am left in the lurches? Is the EE etiquette? Happens more than I care to post.

If you could point me to a document to explains the proper way to use EE, I will certainly read it and abide by it.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I'll do some digging.  

As far as the convoluted answer, yeah that's not good when somebody 'spams' (my word) a question with a less-than-usable answer in such a manner that discourages other experts from replying.  I've certainly been guilty of not commenting in these situaitions, and  I'm not sure what the correct solution to that is, other than maybe do an 'I'm going to delete this question, gather my thoughts, and restate it in a more clear manner.'  Not a perfect solution I realize.

Thanks for taking the time to reply.   We'll call this question good.
0
Scott PletcherSenior DBACommented:
Of course you're not paying the people that actually provide the answers, but the site provider.

I guess it comes to what level of answer you want.  Since you just want to pick a single answer, with no refinements from other experts, I'll leave you to that.  Hopefully the initial answerer will think of every aspect of the problem when he/she posts an answer for you.
0
bobrossi56Author Commented:
In all fairness, when I have received an answer that I used that did involved multiple contributors, I HAVE awarded points to multiple people.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.