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 in time.

I know that the following code will copy (but not change) the rows where the ExistingLanguageID is a certain value:

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 )

Cheers Mike.

I will try this. I am @ work now, but will give this a try when I get home.

Mike, 

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?

Replace 25 with 17 then.

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?

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.

FattMatt:

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 
        FROM NameDetail 
        WHERE NameDetail.UserID = @UserID 
        AND NameDetail.LanguageVersionID = 17)

Is there more to it on your end that makes this unworkable?