Forum Discussion

dariopalermo1976's avatar
dariopalermo1976
Copper Contributor
Feb 13, 2024

Help with delete query

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,...
dariopalermo1976's avatar
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's avatar
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's avatar
    dariopalermo1976
    Copper Contributor
    Feb 23, 2024

    Cheef87 

    The select will only show this output:

     

    rn

    2
    2
    2
    2
    2

     

    bye,

    Dario

    • Cheef87's avatar
      Cheef87
      Copper Contributor
      Mar 04, 2024
      Sorry just seeing this. You just need to include additional columns in your CTE to see more about the rows that are being deleted.

Resources