SQL Update according to unique values

Question

I am busy creating a VB.Net Windows Application. I am using a microsoft sql server database with a table called skedulering. I am trying to update a column (i.e Groep) with unique values based on another column (i.e. Kode). This value takes the first three characters of the Kode value and adds a integer to it. I think what I mean is best explained with the following :

All the red bordered rows have the same Groep value. Can anyone please help me to create the sql statement?

Regards

Why is there no `CAS3` in your example for `CASGrKIRES` ? – GuidoG Oct 03 '18 at 13:05 — GuidoG, Oct 03 '18 at 13:05

score 1 · Accepted Answer · answered Oct 03 '18 at 12:52

1

use dense_rank() to generate a running no

UPDATE t
SET    Groep = left(Kode, 3) + convert(varchar(10), rn)
FROM
(
    SELECT Kode, Groep, rn = dense_rank() over (partition by left(Kode, 3) order by Kode)
    FROM   yourtable
) t

answered Oct 03 '18 at 12:52

Squirrel

23,507
4
34
32

1

Fantastic. Working great. Would never have figured it out on my own. Thank you Squirrel. – Gideon Oct 03 '18 at 15:20

SQL Update according to unique values

1 Answers1