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, 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

  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    Feb 14, 2024
    Please post table design as DDL, some sample data as DML statement and the expected result.
    • 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.

Resources