This question already has an answer here:
-
Update a single row with t-sql
5 answers
I have a very peculiar problem, consider these two rows:
val | text | akr | usr_bk
-----------------------------------------------
001 | hello | 1024 | admin
001 | hello | 1024 | admin
As you see, I have no unique columns or other identificators.
But; Is it possible (and how) to update just one row to have, e.g. val = 002 without modifying the other row?
I'm running on SQL Server 2008.
You're going to need to know which one you want, but you can do it like this with the example data you gave:
;WITH NumberedTbl AS (
SELECT ROW_NUMBER() OVER (
PARTITION BY val, text, akr, admin
ORDER BY val, text, akr, admin
) AS RowNumber,
val, text, akr, admin
FROM tbl
)
UPDATE t
SET val = '002'
FROM NumberedTbl t
WHERE t.RowNumber = 1
SELECT * FROM tbl
see this SqlFiddle for a live example. What you're probably going to want to do is build this and set it up so that you filter the WITH table like this:
;WITH NumberedTbl AS (
SELECT ROW_NUMBER() OVER (
PARTITION BY val, text, akr, admin
ORDER BY val, text, akr, admin
) AS RowNumber,
val, text, akr, admin
FROM tbl
WHERE ... -- ADD SOME WHERE CLAUSE HERE
)
