Help with delete query

dariopalermo1976
Copper Contributor
Feb 14, 2024
olafhelper

USE [GPCHECK]
GO

/****** Object: Table [dbo].[AMP] Script Date: 2/14/2024 11:51:41 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE 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 > 1

But I can't manage to see what records I'm going to delete...

Bye,
Dario
- Cheef87
  Copper Contributor
  Feb 16, 2024
  dariopalermo1976
  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 > 1
  
  This would let you see what was about to happen prior to running. Then you could just replace the select * with the commented Delete.
  - dariopalermo1976
    Copper Contributor
    Feb 23, 2024
    Cheef87
    The select will only show this output:
    
    rn
    2
    2
    2
    2
    2
    
    bye,
    Dario

Forum Discussion

Resources