Using the current row in the update statement

inner-query, row-number, sql, sql-update

Solution

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.

Problem

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?

Original source