Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
newbie04
Starting Member
1 Post |
 Posted - 2013-10-12 : 14:05:02
|
| 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
52326 Posts |
Posted - 2013-10-13 : 01:38:26
|
| 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 solutionsee guidelines to post data herehttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-13 : 20:32:16
|
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? |
|
|
|
|
|
|
|
|
Sql Server Update Script using Where