We help IT Professionals succeed at work.

SQL Update using multiple WHERE conditions

bobrossi56
bobrossi56 asked
on
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
Comment
Watch Question

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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

Commented:
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

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)
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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

Commented:
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
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.

Author

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.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>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.

Author

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.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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.
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.

Author

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.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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.

Author

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.