Using the current row in the update statement

Question

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

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

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

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.

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

Answer 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.