SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Sql Server Update Script using Where
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

newbie04
Starting Member

1 Posts

Posted - 10/12/2013 :  14:05:02  Show Profile  Reply with Quote
I'm a bit new when it comes to sql and creating my own scripts to update tables. I have a question using an update script that I used that seemed to cause an issue.

This is what I used:

update 'Material' set 'Type' = Other, 'TypeID' = 2 where ID = 001

I used this update script to update only columns that matched the specific ID's that I specified. In the example above, I used 001, and for the script, I had 001, 002, and so on. In the where clause, I thought that I would be ok using the ID since it should be unique, but what ended up happening was all records in the table were updated no matter what ID I specified in the script.

What did I do wrong here? Do I need to add another level in the where clause? Maybe where ID = 001 and Entity = 1000?

I would appreciate anyone's help on this!

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/13/2013 :  01:38:26  Show Profile  Reply with Quote
Nobody will be able to suggest anything unless they see how the data is in your table. Post some sample data and then explain what you want as output. then somebody will be able to suggest a solution

see guidelines to post data here

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3589 Posts

Posted - 10/13/2013 :  20:32:16  Show Profile  Reply with Quote
quote:
Originally posted by newbie04

I'm a bit new when it comes to sql and creating my own scripts to update tables. I have a question using an update script that I used that seemed to cause an issue.

This is what I used:

update 'Material' set 'Type' = Other, 'TypeID' = 2 where ID = 001

I used this update script to update only columns that matched the specific ID's that I specified. In the example above, I used 001, and for the script, I had 001, 002, and so on. In the where clause, I thought that I would be ok using the ID since it should be unique, but what ended up happening was all records in the table were updated no matter what ID I specified in the script.

What did I do wrong here? Do I need to add another level in the where clause? Maybe where ID = 001 and Entity = 1000?

I would appreciate anyone's help on this!

Instead of doing the update, first do a select command with the WHERE clause that you think is correct and see what that returns. Those are the rows that will be affected.
SELECT * FROM Material where ID = 001
Also, ID=001 doesn't seem quite right, because if ID is an integer column, 001 is equivalent to 1, and if it is a character column you need to specify ID='001' with the single quotes. Also, under default settings, the statement you posted should have resulted in a syntax error.

Are you using Microsoft SQL Server?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000