I'm attempting to update a table during my database with another row from another table. I've two parameters one being the ID and the other being the row number (as possible choose which row you would like in the GUI)
this area of the code works fine, this returns one column of merely one 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 after i consider adding the update/place it will not work. I'm most likely 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)
Just help! (i've attempted top 1 without any avail)
I visit a couple of difficulties with your update. First, I do not use whatever joining or selection criteria for that table that you are upgrading. This means that every row within the table is going to be up-to-date with this particular new value. Is the fact that really what you would like?
Second, the row number between what's around the GUI and what you'll get in the database might not match. Even when you reproduce the query accustomed to make your list within the GUI (that is harmful anyway, because it involves keeping the update and also the choose code forever in sync), it is possible that somebody could place or remove or update a row between your time that you simply fill your list box and send that row number towards the server for that update. It's Far better to make use of PKs (most likely IDs inside your situation) to find out which row for upgrading.
Nevertheless, I believe the following is useful 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