Torna al Thread
USE tempdb;
GO
-- creo una tabella simile alla tua per prova
CREATE TABLE dbo.position
(
position_id int NOT NULL
, location_id int NULL
, PLN_ID int NOT NULL
, fixel_id int NOT NULL
, CONSTRAINT PK_dboposition PRIMARY KEY CLUSTERED
(
position_id
, PLN_ID
, fixel_id
)
)
GO
-- valori di esempio (c'è anche il 202)
INSERT INTO dbo.position (position_id, location_id, PLN_ID, fixel_id)
VALUES (1, NULL, 10, 10)
INSERT INTO dbo.position (position_id, location_id, PLN_ID, fixel_id)
VALUES (1, NULL, 20, 20)
INSERT INTO dbo.position (position_id, location_id, PLN_ID, fixel_id)
VALUES (1, NULL, 30, 30)
INSERT INTO dbo.position (position_id, location_id, PLN_ID, fixel_id)
VALUES (2, NULL, 10, 10)
INSERT INTO dbo.position (position_id, location_id, PLN_ID, fixel_id)
VALUES (2, NULL, 20, 20)
INSERT INTO dbo.position (position_id, location_id, PLN_ID, fixel_id)
VALUES (202, NULL, 10, 10)
INSERT INTO dbo.position (position_id, location_id, PLN_ID, fixel_id)
VALUES (202, NULL, 20, 20)
INSERT INTO dbo.position (position_id, location_id, PLN_ID, fixel_id)
VALUES (202, NULL, 30, 30)
INSERT INTO dbo.position (position_id, location_id, PLN_ID, fixel_id)
VALUES (202, NULL, 40, 40)
INSERT INTO dbo.position (position_id, location_id, PLN_ID, fixel_id)
VALUES (202, NULL, 50, 50)
INSERT INTO dbo.position (position_id, location_id, PLN_ID, fixel_id)
VALUES (4, NULL, 10, 10)
INSERT INTO dbo.position (position_id, location_id, PLN_ID, fixel_id)
VALUES (4, NULL, 20, 20)
INSERT INTO dbo.position (position_id, location_id, PLN_ID, fixel_id)
VALUES (4, NULL, 30, 30)
INSERT INTO dbo.position (position_id, location_id, PLN_ID, fixel_id)
VALUES (5, NULL, 10, 10)
INSERT INTO dbo.position (position_id, location_id, PLN_ID, fixel_id)
VALUES (5, NULL, 20, 20)
GO
-- dati
SELECT * FROM dbo.position
/*
position_id location_id PLN_ID fixel_id
----------- ----------- ----------- -----------
1 NULL 10 10
1 NULL 20 20
1 NULL 30 30
2 NULL 10 10
2 NULL 20 20
4 NULL 10 10
4 NULL 20 20
4 NULL 30 30
5 NULL 10 10
5 NULL 20 20
202 NULL 10 10
202 NULL 20 20
202 NULL 30 30
202 NULL 40 40
202 NULL 50 50
*/
-- implementazione senza cursore
-- funzione di ranking ROW_NUMBER(), conta finche la chiave di PARTITION BY è uguale, ordinando per i campi definiti in ORDER BY.
;WITH Incrementante AS
(
SELECT
position_id
, location_id = ROW_NUMBER() OVER(PARTITION BY position_id ORDER BY position_id, PLN_ID, fixel_id)
, PLN_ID
, fixel_id
FROM
dbo.position
WHERE
position_id = 202
)
-- aggiornamento dalla WITH (common table expression - CTE)
UPDATE P
SET location_id = I.location_id
FROM
dbo.position P
JOIN Incrementante I ON P.position_id = I.position_id
AND P.fixel_id = I.fixel_id
AND P.PLN_ID = I.PLN_ID
-- dati in sorgente
SELECT * FROM dbo.position ORDER BY position_id DESC, location_id
/*
position_id location_id PLN_ID fixel_id
----------- ----------- ----------- -----------
202 1 10 10
202 2 20 20
202 3 30 30
202 4 40 40
202 5 50 50
5 NULL 10 10
5 NULL 20 20
4 NULL 10 10
4 NULL 20 20
4 NULL 30 30
2 NULL 10 10
2 NULL 20 20
1 NULL 10 10
1 NULL 20 20
1 NULL 30 30
*/
-- pulizia
DROP TABLE dbo.position
GO