更新没有任何标识列的单行 [英] Update a single row without any identification column

问题描述

我有一个很奇怪的问题，考虑这两行:

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.

但是；是否有可能(以及如何)只更新 one 行，例如val = 002 没有修改另一行?

But; Is it possible (and how) to update just one row to have, e.g. val = 002 without modifying the other row?

我在 SQL Server 2008 上运行.

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

查看此 SqlFiddle 的实例.您可能想要做的是构建并设置它，以便像这样过滤 WITH 表:

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
)

这篇关于更新没有任何标识列的单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！