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
Copyright © dotNetHell.it 2002-2025
Running on Windows Server 2008 R2 Standard, SQL Server 2012 & ASP.NET 3.5