My tricky SQL Update query not working so well.

Question

I am trying to update a table in my database with another row from another table. I have two parameters one being the ID and another being the row number (as you can select which row you want from the GUI)

this part of the code works fine, this returns one column of a single row.

    (select txtPageContent
FROM (select *, Row_Number() OVER (ORDER BY ArchiveDate asc) as rowid 
            from ARC_Content Where ContentID = @ContentID) as test
Where rowid = @rowID)

its just when i try to add the update/set it won't work. I am probably missing something

    update TBL_Content
Set TBL_Content.txtPageContent = (select txtPageContent
FROM (select *, Row_Number() OVER (ORDER BY ArchiveDate asc) as rowid 
            from ARC_Content Where ContentID = @ContentID) as test
Where rowid = @rowID) 

Thanks for the help! (i have tried top 1 with no avail)

Answer 1

I see a few issues with your update. First, I don't see any joining or selection criteria for the table that you're updating. That means that every row in the table will be updated with this new value. Is that really what you want?

Second, the row number between what is on the GUI and what you get back in the database may not match. Even if you reproduce the query used to create your list in the GUI (which is dangerous anyway, since it involves keeping the update and the select code always in sync), it's possible that someone could insert or delete or update a row between the time that you fill your list box and send that row number to the server for the update. It's MUCH better to use PKs (probably IDs in your case) to determine which row to use for updating.

That said, I think that the following will work for you (untested):

;WITH cte AS (
    SELECT
        txtPageContent,
        ROW_NUMBER() OVER (ORDER BY ArchiveDate ASC) AS rowid
    FROM
        ARC_Content
    WHERE
        ContentID = @ContentID)
UPDATE
    TC
SET
    txtPageContent = cte.txtPageContent
FROM
    TBL_Content TC
INNER JOIN cte ON
    rowid = @rowID