SQL Update Records with Row_Number()

SQL Update with row_number()

With UpdateData  As
(
SELECT RS_NOM,
ROW_NUMBER() OVER (ORDER BY [RS_NOM] DESC) AS RN
FROM DESTINATAIRE_TEMP
)
UPDATE DESTINATAIRE_TEMP SET CODE_DEST = RN
FROM DESTINATAIRE_TEMP
INNER JOIN UpdateData ON DESTINATAIRE_TEMP.RS_NOM = UpdateData.RS_NOM

SQL update records with ROW_NUMBER()

Let me assume that cards has a primary key. Then you can use join:

update cards c
    set position = c2.seqnum
    from (select c2.*, row_number() over () as seqnum
          from cards c2
         ) c2
    where c2.pkid = c.pkid;

I should note that the over () looks strange but Postgres does allow it. Normally an order by clause would be included.

Update a column based on row_number()

Use This Code:

 Create  TABLE #order
    (
        Id INT,
        PersonId INT,
        Name NVARCHAR(25),
        DisplayOrder INT
    )

    INSERT INTO #ORDER VALUES(1 , 10 , 'test1',0 )
    INSERT INTO #ORDER VALUES(2 , 10 , 'test2',0 )
    INSERT INTO #ORDER VALUES(3 , 10 , 'test3',0 )
    INSERT INTO #ORDER VALUES(4 , 10 , 'test4',0 )
    INSERT INTO #ORDER VALUES(5 , 10 , 'test5',0 )
    INSERT INTO #ORDER VALUES(6 , 11 , 'test6',0 )
    INSERT INTO #ORDER VALUES(7 , 11 , 'test7',0 )
    INSERT INTO #ORDER VALUES(8 , 12 , 'test8',0 )

 update #order 
    Set #order.DisplayOrder=R.DisplayOrder
    from(select id,ROW_NUMBER() over (partition by S.personid  order by S.id) as DisplayOrder
    from #order S) R
    where #order.Id=R.id

    select * from #order

How to update a column with ROW_NUMBER() in SQL

Try this:

UPDATE T
SET T.rownumber = TT.ROW_ID
FROM tab1 AS T
INNER JOIN (SELECT ROW_NUMBER() OVER (ORDER BY name) AS ROW_ID
                  ,name
            FROM Tab1) AS TT
    ON T.name = TT.name

Using Row_Number() with Update Statement

You are trying to update a column (countrow) that doesn't exist in your table so you will need to add this column to your original table and join to the subquery:

/* add the column */
ALTER TABLE usertable ADD countrow INT;

/* do the update */
UPDATE  usertable
SET     countrow = countrow_new
FROM ( 
        SELECT  userid, 
                createddate, 
                bookid, 
                pagenumber,
                CASE 
                    WHEN ROW_NUMBER() OVER (PARTITION BY userid, bookid ORDER BY bookid) = 1
                    THEN SUM(pagenumber) OVER (PARTITION BY userid, bookid) 
                END AS countrow_new
        FROM    usertable
      ) x
      JOIN usertable u
        ON u.bookid = x.bookid AND u.createddate = x.createddate AND u.userid = x.userid;

The above assumes {bookid,createddate,userid} will always be unique

However, storing calculations in a table this way is very bad design and bad practice - you will have to keep the column up to date with a trigger on every insert / update / delete which will be terrible for performance.

A better approach would be to take your original query and create a view for it which can then be used as though it was a table:

create the view:

CREATE VIEW vwMyView
AS
SELECT  userid, 
        bookid, 
        pagenumber,
        CASE 
            WHEN ROW_NUMBER() OVER (PARTITION BY userid, bookid ORDER BY bookid) = 1 
            THEN SUM(pagenumber) OVER (PARTITION BY userid, bookid) 
        END AS countrow
FROM    usertable;

Select from the view:

SELECT * FROM vwMyView;

How to update a column value with ROW_NUMBER() OVER value based on distinct values of a column

You can use dense_rank() for this purpose:

select name, id, dense_rank() over (order by minid) as seqno
from (select t.*, min(id) over (partition by name) as minid
      from table t
     ) t;

If you wanted to do this just with row_number():

select t.name, t.id, tt.seqnum
from table t join
     (select t.name, row_number() over (order by min(id)) as seqno
      from table t
      group by t.name
     ) tt
     on t.name = tt.name;

However, I don't know why you would want to do that.

Update table through row_number()

So say you; that's different in my database:

SQL> select nome,
  2         row_number() over (order by null) r
  3  from anagrafica;

NOME            R
------ ----------
mrco            1
fabio           2     --> 2 isn't "mrco" but "fabio"
marco           3 
mchele          4

SQL>

How come? Because ORDER BY NULL is as good as if there were no ORDER BY clause. In other words, those row numbers are useless; you could've used ROWNUM and get the same final result (useless).

How can you tell whether "fabio" is incorrect (or correct)? Or - OK, let it be "mrco" - what will you update its value to?

From my point of view, the only meaningful option is

SQL> update anagrafica set nome = 'marco' where nome = 'mrco';

1 row updated.

---

Anyway: if it were a problem that actually makes sense, rowid pseudocolumn might be useful. Here's how:

SQL> select nome,
  2         row_number() over (order by null) r,
  3         rowid
  4  from anagrafica;

NOME            R ROWID
------ ---------- ------------------
mrco            1 AAAF3xAAEAAAAuDAAA
fabio           2 AAAF3xAAEAAAAuDAAB
marco           3 AAAF3xAAEAAAAuDAAC
mchele          4 AAAF3xAAEAAAAuDAAD

SQL>

Use it in update as:

SQL> update anagrafica a set
  2    a.nome = 'little'
  3    where a.rowid = (select b.rwd
  4                     from (select row_number() over (order by null) r,
  5                                  rowid rwd
  6                           from anagrafica
  7                          ) b
  8                     where b.r = 2
  9                    );

1 row updated.

SQL> select * from anagrafica;

NOME
------
mrco
little
marco
mchele

SQL>

It updated row for which row_number analytic function returned value equal to 2. Once again - as you put it, you can't really tell which row it is (and certainly can't update it to a "valid" value because you don't know what you're updating so you can't specify a new value).

How to use ROW_NUMBER() in UPDATE clause?

DECLARE @MyTable TABLE
(
    ID INT IDENTITY(2,2) PRIMARY KEY,
    MyNum INT,
    ColA INT,
    ColB INT
);

INSERT  @MyTable (ColA, ColB)
SELECT 11, 11 UNION ALL
SELECT 22, 22 UNION ALL
SELECT NULL, NULL UNION ALL
SELECT 33, NULL UNION ALL
SELECT NULL, 44 UNION ALL
SELECT 55, 66;

UPDATE  UpdateTarget
SET     MyNum = RowNum
FROM
(
    SELECT  x.MyNum, ROW_NUMBER() OVER(ORDER BY x.ID) AS RowNum
    FROM    @MyTable x
    WHERE   x.ColA = x.ColB
) AS UpdateTarget;

SELECT * FROM @MyTable;

Results:

ID          MyNum       ColA        ColB
----------- ----------- ----------- -----------
2           1           11          11
4           2           22          22
6           NULL        NULL        NULL
8           NULL        33          NULL
10          NULL        NULL        44
12          NULL        55          66

---

Related Topics