Last post Jul 08, 2009 02:05 PM by Naom
Jul 04, 2008 09:15 PM|FattMatt|LINK
I am trying to write a stored procedure to copy all rows within a table of a user where the LanguageVersionID is say 17, and replace the LanguageVersionID with a new
value of say 25, but keeping all other values the same. The two values (ExistingLanguageID & NewLanguageID) are derived from two drop down lists located on one of my web pages. But the code to change the LanguageVersionID is beyond my skill set at this point
I know that the following code will copy (but not change) the rows where the ExistingLanguageID is
a certain value:
Jul 04, 2008 09:34 PM|mbanavige|LINK
include a literal value in your select statement that is the value you wish to insert.
INSERT INTO NameDetail (NameDetail.FirstName, NameDetail.MiddleName, NameDetail.LastName, NameDetail.LanguageVersionID, NameDetail.UserID)
(SELECT NameDetail.FirstName, NameDetail.MiddleName, NameDetail.LastName, 25, NameDetail.UserID FROM NameDetail WHERE NameDetail.UserID = @UserID AND NameDetail.LanguageVersionID = @ExistingLanguageID )
Jul 04, 2008 09:36 PM|FattMatt|LINK
I will try this. I am @ work now, but will give this a try when I get home.
Jul 04, 2008 10:17 PM|FattMatt|LINK
Your suggested use of the literal field only copies the row as my suggested code does. It does not change the LanguageVersionID from 25 to 17.
Do I have to add a 2nd sub query or can I add an update command to the code?
Jul 04, 2008 10:28 PM|limno|LINK
Replace 25 with 17 then.
Jul 04, 2008 10:46 PM|FattMatt|LINK
25 is the new value of the LanguageVersionID that is to replace the current value of 17, so currently there are no LanguageVersionID's of 25, so replacing 25 with 17 would insert 0 values.
Essentially I am inserting a new row, then trying to update the LanguageVersionID of the updated row from 17 to 25.
Any other suggestions?
Jul 04, 2008 11:40 PM|limno|LINK
Actually, the code is not replacing anything here. It is inserting the value based on the SELECT statement, for LanguageVersionID column, instead of using the value from the SELECT statement, it is inserting the number 25 in that column. If the result is
not what you are expecting, you need to check the WHERE clause of your SELECT statement.
Jul 05, 2008 06:34 AM|mbanavige|LINK
I am inserting a new row, then trying to update the LanguageVersionID of the updated row from 17 to 25
The suggestion that i made would not affect the original data. it changes the LanguageVersionID to 25 for the new rows being inserted. It's only a single insert statement so only the inserted data is going to be affected. Since you indicated you were copying
all the values over from the existing row to the new row, it shouldnt matter if the new value of 25 lands in the old rows or the new rows.
So in this query, we are selecting rows with a LanguageID of 17, but we are inserting those selected rows as copies with a LanguageID of 25
INSERT INTO NameDetail (NameDetail.FirstName, NameDetail.MiddleName,
NameDetail.LastName, NameDetail.LanguageVersionID, NameDetail.UserID)
(SELECT NameDetail.FirstName, NameDetail.MiddleName,
NameDetail.LastName, 25, NameDetail.UserID
WHERE NameDetail.UserID = @UserID
AND NameDetail.LanguageVersionID = 17)
Is there more to it on your end that makes this unworkable?
Jul 08, 2009 12:12 PM|mandercruso|LINK
I'm having the same problem but instead of using a literal for the new value, I need to use a parameter value. The "old" value would be @previousyear anad the new value would be @currentyear. How can I do this?
Thanks in advance,
Jul 08, 2009 12:24 PM|Naom|LINK
Just use this parameter in your insert statement, e.g.
insert into myTable ([Year], field1, field2) select @CurrentYear as [Year], field1, field2 from myOtherTable where [Year] = @PreviousYear
Jul 08, 2009 01:50 PM|mandercruso|LINK
Works perfect. Thanks!!!!
Jul 08, 2009 02:05 PM|Naom|LINK
You're welcome. Too bad I can not get an answer mark on the old thread :)