使用update语句中的当前行

What I'm trying to do is the following: I have a table with multiple authors SingleAuthor. This table sometimes contains the same author more than once. What I want to do is update the table and add the author specific number. For instance:

我要做的是:我有一个包含多个作者的单作者表。此表有时不止一次包含同一作者。我要做的是更新表并添加作者特定的数字。例如:

sat_name -> sat_rowNumber
Freddy -> 1
Author2 -> 2
Freddy -> 1
AnotherOne -> 3

sat_name -> sat_rowNumber Freddy -> 1 Author2 -> 2 Freddy -> AnotherOne -> 3

I already have the query that gives me this results:

我已经有了给我这个结果的查询:

SELECT ROW_NUMBER() OVER( ORDER BY sat_name), sat_name FROM SingleAuthor GROUP BY sat_name

通过(按sat_name排序)选择ROW_NUMBER()，通过sat_name从SingleAuthor组中选择sat_name

The problem however is, that I want to insert this data in the sat_rowNumber column. I came this far with the query:

但问题是，我想在sat_rowNumber列中插入这些数据。我提出了这个问题:

UPDATE SingleAuthor SET sat_rowNumber = (   SELECT newTable.numb 
                                            FROM(
                                                SELECT ROW_NUMBER() OVER( ORDER BY sat_name) as numb, sat_name 
                                                FROM SingleAuthor  
                                                GROUP BY sat_name) AS newTable 
                                            WHERE newTable.sat_name =) -- the current row in the to be updated table

What I want to do is update the SingleAuthor table's sat_rowNumber to the newTable.numb where the current row sat_name is equal to the sat_name in the newTable.

我要做的是将SingleAuthor表的sat_rowNumber更新到newTable。麻木，其中当前行sat_name等于newTable中的sat_name。

Any insights on how I can reference the to be updated table within the update statement?

关于如何在update语句中引用待更新表有什么见解吗?

1 个解决方案

#1

The answer to your question is:

你的问题的答案是:

where newTable.sat_name = SingleAuthor.sat_name

It will reference the outer table, because the one in the subquery is out of scope. If this were a problem, though, you could give the one in the subquery a different alias.

它将引用外部表，因为子查询中的表超出了范围。但是，如果这是一个问题，您可以在子查询中给出一个不同的别名。

I think you can write the query more efficiently as:

我认为您可以更有效地编写查询:

with toupdate as (
    select sa.*, dense_rank() over (order by sat_name) as newVal
    from SingleAuthor sa
)
update toupdate
    set sat_RowNumber = newval

The dense_rank() function does exactly what you are doing with row_number() on the aggregated values.

函数的作用是:对聚合值执行row_number()操作。

#1