2 123
3 456
4 456
5 789
6 789
Change
1 123-1
2 123-2
3 456-1
Four 456-2
5, 789-1
June 789-2
7, 789-3
What is the SQL statement?
CodePudding user response:
The CREATE TABLE # T
(
Id INT,
Val VARCHAR (100)
)
INSERT INTO # T VALUES (1, 123)
INSERT INTO # T VALUES (2, 123)
INSERT INTO # T VALUES (3, 456)
INSERT INTO # T VALUES (4, 456)
INSERT INTO # T VALUES (5, 789)
INSERT INTO # T VALUES (6, 789)
Method - a
; WITH ct
AS
(
SELECT *, ROW_NUMBER () OVER (PARTITION BY val ORDER BY id) AS the FROM x # T
)
The UPDATE ct SET val=CONCAT (val, '-', x)
- the method 2
UPDATE # T SET val=CONCAT (val, '-' (SELECT COUNT (1) the FROM # T a WHERE clause val=# T.v al AND id<=# T.i d))
DROP TABLE # T
CodePudding user response:
Int the create table # t (a, b varchar (10))
Insert into # t (a, b)
Select 1, '123' union all
Select 2, '123' union all
Select 3, '456' union all
Select 4, '456' union all
Select 5, '789' union all
Select 6, '789' union all
Select 7, '789'
The update t
The set b=b + '-' + rtrim ((select count (1)
The from # t u
Where u.b=t.b
And u.a & lt;=t.a))
The from # t t
Select * from # t
/*
A, b
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
1 123-1
2 123-2
3 456-1
Four 456-2
5, 789-1
June 789-2
7, 789-3
(7) affected
*/