Help with delete query

[dariopalermo1976]

Hi guys,

I need help creating a delete query...

The table has 4 relevant fields:

ComputerName (nvarchar)

LastContact (datetime)

guid (nvarchar)

When something changes on the computers, the guid value changes so I can have multiple records for the same ComputerName with different guid values. After I review the changes, I want to delete the older records (based on LastContact)

Example:

PLI7285.COMPA.LOCAL 2024-02-07 12:47:48.747 241ca249-2302-4c70-a789-a68580ab473d
PLI7285.COMPA.LOCAL 2024-02-13 09:41:03.727 66f574d1-4d0c-49ca-9458-28834a9db927

I need to delete that first row.

Any suggestion? Thanks.

bye

Dario

[#2]

Please post table design as DDL, some sample data as DML statement and the expected result.

[#3]

@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

[#4]

@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.

[#5]

@Cheef87 

The select will only show this output:

rn

2
2
2
2
2

bye,

Dario

[#6]

Sorry just seeing this. You just need to include additional columns in your CTE to see more about the rows that are being deleted.