November 13, 2015 at 1:55 am
Hi everybody, can i have your help ?
i have this table
CREATE TABLE [dbo].[OrdinamentiRgpPerCard](
[idRgp] [tinyint] NOT NULL,
[idTipologiaCard] [smallint] NOT NULL,
[OrdRgp] [tinyint] NOT NULL,
CONSTRAINT [PK_OrdinamentiRgpPerCard] PRIMARY KEY CLUSTERED
(
[idRgp] ASC,
[idTipologiaCard] ASC,
[OrdRgp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
and this data
INSERT dbo.OrdinamentiRgpPerCard VALUES (1, 2, 0)
INSERT dbo.OrdinamentiRgpPerCard VALUES (1, 28, 0)
INSERT dbo.OrdinamentiRgpPerCard VALUES (2, 1, 0)
INSERT dbo.OrdinamentiRgpPerCard VALUES (2, 28, 1)
INSERT dbo.OrdinamentiRgpPerCard VALUES (3, 1, 1)
INSERT dbo.OrdinamentiRgpPerCard VALUES (3, 2, 1)
INSERT dbo.OrdinamentiRgpPerCard VALUES (3, 28, 2)
INSERT dbo.OrdinamentiRgpPerCard VALUES (4, 1, 2)
INSERT dbo.OrdinamentiRgpPerCard VALUES (5, 1, 3)
i wont to delete where idrgp = 2 (easy)
and after, in 1 query
obtain this result
INSERT dbo.OrdinamentiRgpPerCard VALUES (1, 2, 0)
INSERT dbo.OrdinamentiRgpPerCard VALUES (1, 28, 0)
INSERT dbo.OrdinamentiRgpPerCard VALUES (3, 1, 0)
INSERT dbo.OrdinamentiRgpPerCard VALUES (3, 2, 1)
INSERT dbo.OrdinamentiRgpPerCard VALUES (3, 28, 1)
INSERT dbo.OrdinamentiRgpPerCard VALUES (4, 1, 1)
INSERT dbo.OrdinamentiRgpPerCard VALUES (5, 1, 2)
update OrdinamentiRgpPerCard set OrdRgp = OrdRgp - 1 where ....
thank you !
November 13, 2015 at 9:13 am
i think it works
;With UpdateData As
(
SELECT idRgp, idTipologiaCard, OrdRgp, (ROW_NUMBER() OVER (PARTITION BY idTipologiaCard ORDER BY ordrgp) -1) AS Ordinamento FROM OrdinamentiRgpPerCard
WHERE idTipologiaCard IN (SELECT idTipologiaCard FROM OrdinamentiRgpPerCard WHERE idRgp = @idRgp)
AND idRgp <> @idRgp
)
UPDATE OrdinamentiRgpPerCard SET OrdinamentiRgpPerCard.OrdRgp = UpdateData.Ordinamento
FROM OrdinamentiRgpPerCard
INNER JOIN UpdateData ON OrdinamentiRgpPerCard.idRgp = UpdateData.idRgp AND OrdinamentiRgpPerCard.idTipologiaCard = UpdateData.idTipologiaCard
DELETE OrdinamentiRgpPerCard WHERE idRgp = @idRgp
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply