CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012sql-update
davinceleecode
davinceleecode

Reputation: 807

Update Value in same Primary Key

I'm trying to update values in the same primary key in which is in 1st row value will apply to the second sequence until the end of the sequence with Primary Key Value.

Here is my sample scenario

enter image description here

This should be the expected output.

enter image description here

I don't want also to be updated the rows that are already have values. Is this possible? I don't have any idea on how to achieve with this output. Can anyone help me? I am using SQL.

Thanks

Upvotes: 0

Views: 724

Answers (2)

Randy in Marin
Randy in Marin

Reputation: 1143

The DENSE_RANK windowed function is handy for this type of sequence. If the goal is to just return the value, there is no need to store it as it can be obtained directly in the select.

DECLARE @Test as Table (
    Id char(1),
    Seq int,
    Val varchar(10)
)

INSERT @Test (Id, Seq)
VALUES
    ('A','1'),
    ('A','2'),
    ('A','3'),
    ('A','4'),
    ('A','5'),
    ('B','1'),
    ('B','2'),
    ('B','3'),
    ('B','4'),
    ('C','4'),
    ('C','5');

-- Select Only

SELECT Id, Seq, 
    'Test' + LTRIM(STR(DENSE_RANK() OVER (ORDER BY Id))) as [Val]
FROM @Test

-- Update via derived table

UPDATE t
SET Val = 'Test' + LTRIM(STR(Seq2))
FROM @Test t
INNER JOIN (
    SELECT Id, Seq, DENSE_RANK() OVER (ORDER BY Id) as [Seq2]
    FROM @Test
) v
ON v.Id = t.Id AND v.Seq = t.Seq

Upvotes: 0

GMB
GMB

Reputation: 222652

You can use window functions and an updatable CTE.

If there is only one non-null value per id, then a window min or max is sufficient:

with cte as (
    select value, min(value) over(partition by id) as new_value
    from mytable t
)
update cte set value = new_value where value is null

If you specifically want the row where sequence has value 1, use conditional window aggregation:

with cte as (
    select value, 
        min(case when sequence = 1 then value end) over(partition by id) as new_value
    from mytable t
)
update cte set value = new_value where value is null

Finally, if you want the row with the minimum sequence, regardless of the its actual value:

with cte as (
    select value, 
        first_value(value) over(partition by id order by sequence) as new_value
    from mytable t
)
update cte set value = new_value where value is null

Upvotes: 3

Related Questions