Forum Discussion
Help with delete query
- dariopalermo1976Feb 14, 2024Copper ContributorUSE [GPCHECK]GO/****** Object: Table [dbo].[AMP] Script Date: 2/14/2024 11:51:41 AM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[AMP]([ComputerName] [nvarchar](38) NOT NULL,[LastContact] [datetime] NOT NULL,[guid] [nvarchar](50) NOT NULL,[IPAddress] [nvarchar](45) NOT NULL,CONSTRAINT [PK_AMP] PRIMARY KEY CLUSTERED([ComputerName] ASC,[guid] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]GO
insert into AMP VALUES ('PLI10161.COMPA.LOCAL','2024-02-14 11:50:40.807','9530c214-9aa5-4a27-85d7-8f9488237253','172.30.1.126')
insert into AMP VALUES ('PLI10161.COMPA.LOCAL','2024-02-08 13:30:01.403','e34a0c47-b2dc-4562-8475-ece13ae729c3','192.168.26.91')I have to delete the "older" (based on LastContact") record with same ComputerName
I was able to adapt a query I found online:
;WITH ToDelete AS (
SELECT ROW_NUMBER() OVER (PARTITION BY ComputerName
ORDER BY LastContact DESC) AS rn
FROM AMP
)
DELETE FROM ToDelete
WHERE rn > 1But I can't manage to see what records I'm going to delete...
Bye,
Dario
- Cheef87Feb 16, 2024Copper Contributor
You could try to just do something like this to see what will happen:
;WITH ToDelete AS (
SELECT ROW_NUMBER() OVER (PARTITION BY ComputerName
ORDER BY LastContact DESC) AS rn
FROM AMP
)SELECT *
--DELETE
FROM ToDelete
WHERE rn > 1This would let you see what was about to happen prior to running. Then you could just replace the select * with the commented Delete.
- dariopalermo1976Feb 23, 2024Copper Contributor