May 21, 2013 at 8:38 am
I have an existing table where if I use a Select statement against one of the columns I could get 6 rows of data back.
I need to update the table in order to add another row based on a particular value in the column in my select statement.
I tried using this update statement:
update table_name set column_name = value where other_column_name = value
My results were not as desired as it change all the other values for the column_name to the new value.
Here is an example of my select statement on the other_column_name before my update statement:
column_name____other_column_name
0001_______________column5
0002_______________column5
0003_______________column5
0004_______________column5
0005_______________column5
0006_______________column5
Here is the result of my update statement using a new value for column_name of '0007':
column_name____other_column_name
0007_______________column5
0007_______________column5
0007_______________column5
0007_______________column5
0007_______________column5
0007_______________column5
It makes sense what happened. I'm just not sure how to get the results I want which is:
column_name____other_column_name
0001_______________column5
0002_______________column5
0003_______________column5
0004_______________column5
0005_______________column5
0006_______________column5
0007_______________column5
Thanks for your help on this.
May 21, 2013 at 8:47 am
The result you want is exactly the same as what you have.
Why are trying to update it or am i missing something?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 21, 2013 at 8:47 am
Hi and welcome to the forums. It sounds like you need some help with an update (or maybe an insert). From what you posted you are not likely to get much help because you really didn't post very much detail. It would be greatly helpful if you could post ddl (create table scripts), sample data (insert statements) and desired output. Please take a few minutes to read the first article in my signature about best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 21, 2013 at 8:50 am
I need to update the table in order to add another row based on a particular value in the column in my select statement.
I looked and looked at your original post and this just makes no sense to me. An update will NOT generate new rows. An insert is the ONLY way to generate new rows. The more I read your original post the more confused I get.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 21, 2013 at 8:50 am
The result I want actually has one more line added to it. I need 7 unique records (for example) instead of the original 6.
May 21, 2013 at 8:51 am
tknecht 32495 (5/21/2013)
The result I want actually has one more line added to it. I need 7 unique records (for example) instead of the original 6.
Then you need to insert a new row. There is no update that will generate more rows.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 21, 2013 at 8:57 am
Sorry for the confusion. I'm obviously not an expert.
The searching I've done for doing what I need suggests using an update statement to add information to an existing table. I was lead to believe that an insert statement is only used for inserting a new table. If I'm wrong on that then I guess that's why I'm here.
I guess I'll read the article I'm supposed to read as suggested and post some better information.
May 21, 2013 at 9:03 am
tknecht 32495 (5/21/2013)
The searching I've done for doing what I need suggests using an update statement to add information to an existing table. I was lead to believe that an insert statement is only used for inserting a new table.
Not sure where you read that but it is not correct.
Update - This statement is used to change the value of an existing row.
Insert - This statement is used to insert a new row into an existing table.
Once you post the ddl and such I am confident we can help you solve the issue you are facing. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 21, 2013 at 9:05 am
tknecht 32495 (5/21/2013)
Sorry for the confusion. I'm obviously not an expert.The searching I've done for doing what I need suggests using an update statement to add information to an existing table. I was lead to believe that an insert statement is only used for inserting a new table. If I'm wrong on that then I guess that's why I'm here.
I guess I'll read the article I'm supposed to read as suggested and post some better information.
You will have to use an INSERT statement like below
INSERT is used to add rows to the table
INSERTtable_name( column_name, other_column_name)
VALUES( '0007', 'column5')
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 21, 2013 at 9:06 am
Insert was actually the first thing I tried. This is my insert statement using the actual table and column names:
insert into Component (CmpnentID) values (8001090000) where KitID = '2560150001'
The hiccup is the 'where' clause. I get the follow results:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'where'.
I know my statement is incorrect I just don't know what needs to be changed.
May 21, 2013 at 9:08 am
Once you post the ddl and such I am confident we can help you solve the issue you are facing. :-D[/quote]
Thanks. I appreciate that. Give me a bit. You all are posting so fast I can't keep up. 🙂
May 21, 2013 at 9:08 am
tknecht 32495 (5/21/2013)
Insert was actually the first thing I tried. This is my insert statement using the actual table and column names:insert into Component (CmpnentID) values (8001090000) where KitID = '2560150001'
The hiccup is the 'where' clause. I get the follow results:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'where'.
I know my statement is incorrect I just don't know what needs to be changed.
A where clause is used to filter which records are returned from a query. An insert statement cannot have a where clause, it doesn't make sense. You are inserting the values.
try this:
insert into Component (CmpnentID) values (8001090000)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 21, 2013 at 9:28 am
A where clause is used to filter which records are returned from a query. An insert statement cannot have a where clause, it doesn't make sense. You are inserting the values.
try this:
insert into Component (CmpnentID) values (8001090000)
[/quote]
I would love to use that script but I'm worried it's not specific enough as to where I want the insert. Let me explain (hopefully). The table I'm inserting into (Component) is part of a financial system in the inventory module and the table itself is about 50 columns. The two key columns I'm dealing with is CmpnentID and KitID.
When I run this the below statement:
select CmpnentID, KitID, * from Component
I get these results:
0RCRANK,0RCRANK-ASSMBLY
0RPEDAL,0RCRANK-ASSMBLY
0RNUT,0RCRANK-ASSMBLY
0RBOLT,0RCRANK-ASSMBLY
0RHANDLBAR,0RETRO24
0RFRAME24,0RETRO24
0RFRONTWHEEL24,0RETRO24
0RREARWHEEL24,0RETRO24
0RCRANK-ASSMBLY,0RETRO24
0RCHAIN,0RETRO24
0RCHAINGUARD,0RETRO24
0RSEATASSMBLY,0RETRO24
0RNUT,0RETRO24
0RBOLT,0RETRO24
Looking at my results, you can see the same value for KitID listed several times but the CmpnentID is unique.
I need a way to add, for instance, to insert CmpnentID '0RASSEMBLY' to KitID '0RETRO24' in the Component table.
I really hope I'm making better sense. 😀
May 21, 2013 at 9:55 am
It is tough to know exactly what you have going on because I sadly can't see your screen. 😉
I would love to use that script but I'm worried it's not specific enough as to where I want the insert.
This scares me quite a bit. The insert will always happen on that table. It can't possibly go anywhere else. I think that what you mean is you have not specified enough columns??
Maybe something like this?
insert into Component (CmpnentID, KitID) values (8001090000, '2560150001')
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 21, 2013 at 10:03 am
I understand that the insert would absolutely work as written. My concern was that it wouldn't go into the correct KitID. Sorry if my comments offended you - it was not my intent.
I think you have given me the answer I was looking for. I really do appreciate it.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply