Can I use lag function in an update function to update a variable table

userOmA 0
Viewed 43

I have this @variable table with an initial row. I'd like to update the following rows based on that first row.

DECLARE @varTable1 Table
(
[id] [int],
[field1] [decimal](18,4)
)

INSERT INTO @varTable1
VALUES
(1,20),
(1,NULL),
(1,NULL),
(1,NULL)

SELECT * FROM @varTable1

Let's just say I want to multiply field1 by 2. So the expected values following inital row would be

1 20
2 40
3 60
4 80

enter image description here

sql-server sql
2 Answers

One way is to get the min value and multiple it by a row number - assuming your id column should actually be incrementing rather than all 1's e.g.

WITH cte AS (
    SELECT *
        , MIN(field1) OVER () * ROW_NUMBER() OVER (ORDER BY id ASC) newField1
    FROM @varTable1
)
UPDATE cte SET field1 = newField1;
Dale K0

I guess id is important so you have to multiple field of same id

DECLARE @varTable1 Table
(
[id] [int],
[field1] [decimal](18,4)
)

INSERT INTO @varTable1
VALUES
(1,20),
(1,NULL),
(1,NULL),
(1,NULL),
(2,NULL)
;with CTE as
(
SELECT *,row_number()over(order by id)rn FROM @varTable1
),
CTE1 as
(
select id, min([field1])Minfield from @varTable1
group by [id]
)


select vt.id,vt.field1,c.Minfield*rn from CTE VT
inner join CTE1 c on vt.id=c.id
KumarHarsh0
Related