0

I feel like this should be easy but I'm having the hardest time. I'm using SQL server. I'm trying to get the output from the following query to be used in an update statement to add to my table:

SELECT cons_id, credited_date,
    ROW_NUMBER() OVER (partition by cons_id order by cons_id) AS Row_Counter
FROM fy23_jul_aug_anniv_jv;

My primary key is cons_id. The output looks like this:

enter image description here

I want the row_counter column to be a permanent column in my table called COUNTER (which I already added to my table...I just need to update it). I've tried this but I get an error saying

SQL command not properly ended.

UPDATE fy23_jul_aug_anniv_jv 
SET counter = b.row_counter
FROM (SELECT cons_id, ROW_NUMBER() OVER (partition by cons_id order by cons_id) AS Row_Counter
        FROM fy23_jul_aug_anniv_jv) AS b
WHERE b.cons_id = cons_id;
Thom A
  • 88,727
  • 11
  • 45
  • 75
jlvolsch
  • 1
  • 1
  • 3
    Don't store the `ROW_NUMBER`; use a `VIEW`. Otherwise as soon as a row is `INSERTED`, `DELETE` or even `UPDATE`d, the value of your `COUNTER` column is out of date. – Thom A Jul 07 '22 at 15:51
  • 1
    Assuming you have multiple rows per cons_id, you may want to use a different column in the order by for your row_number. – Andrew Jul 07 '22 at 15:54
  • If `cons_id` is your primary key, then for each value of `cons_id` there can only be **one** row; having a `PARTITION BY` on `cons_id` therefore doesn't make any sense; there will always be *only **one*** row per distinct value of `cons_id`. – Thom A Jul 07 '22 at 16:00

1 Answers1

0

You can use the common table expression (CTE)

WITH CTE AS (
    SELECT counter ,ROW_NUMBER() OVER (partition by cons_id order by cons_id) AS Row_Counter
    FROM fy23_jul_aug_anniv_jv
)
UPDATE CTE SET counter = Row_Counter
Mostafa NZ
  • 382
  • 1
  • 6