Update a single row without any identification column

Question

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.

Check this answer: http://stackoverflow.com/a/9862688/1618257 (though instead of DELETE you can use UPDATE and an incrementing variable if necessary) — David Starkey, Mar 20 '13 at 15:22

score 4 · Accepted Answer · answered Mar 20 '13 at 15:33

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
)

Update a single row without any identification column

1 Answers1

Linked