How do you create a delete query that deletes rows with criteria?

Question

I have an insert query that goes into a table linked to QuickBooks. The table Test_InvoiceLine has a lot of Part_ID's and Descriptions that are exactly the same.

INSERT INTO InvoiceLine (Part_ID, More_Info  )
SELECT Part_ID, Description 
FROM Test_InvoiceLine;

How can I write a query that goes into the InvoiceLine table and deletes duplicates with the same Part_ID and Description that are already there?

Also, you’re inserting `Description` into `Part ID`... that seems off. Can you please check the query you posted? — Bohemian, Jan 30 '19 at 18:13
Please add the tag for the particular database you’re using. — Bohemian, Jan 30 '19 at 19:17

Ross Bush · Answer 1 · 2019-01-30T18:49:17.290

This is SQL Server, however, ROW_NUMBER is widely used in other RDMS.

Here is the query you need.

;WITH Data AS

(
    SELECT 
        Part_ID,Description,
        RowNumber = ROW_NUMBER() OVER(PARTITION BY  Part_Id,Description ORDER BY Part_Id,Description)
    FROM  Test_InvoiceLine
)
DELETE FROM Data WHERE RowNumber > 1

I don't know how More_Info will make a difference here as the duplicate key does not include it, according to your post, however, if you need to inspect the more_info values in the delete statement then perhaps you could use something similar to the query below.

;WITH Data AS
(
    SELECT  
        More_Info,
        Part_ID,Description,
        RowNumber = ROW_NUMBER() OVER(PARTITION BY  Part_Id,Description ORDER BY Part_Id,Description)
    FROM  Test_InvoiceLine    
)

DELETE T
FROM Test_InvoiceLine T
INNER JOIN Data D ON D.RowNumber > 1 AND D.MoreInfo = "Y" AND D.Part_Id = T.Part_ID

Ok that seems close to what I'm look for, but I need it to delete values based on two parameters. Maybe something like WHERE Part_ID and More_Info is > 1 — , Jan 30 '19 at 18:24

score 0 · Accepted Answer · answered Jan 30 '19 at 18:20

Use a CTE as a temporary result set. You essentially query for duplicate records that are partitioned by your criteria, and delete the records that are not the first record (keeps the original record).

Double-check me before you blow anything away, because it has been a while since I've done this, but this should work.

  WITH CTE AS(
       SELECT Part_ID, Description,
           RowNum = ROW_NUMBER()OVER(PARTITION BY Part_ID, Description ORDER BY Part_ID, Description)
       FROM Test_InvoiceLine
    )
    DELETE FROM CTE WHERE RowNum > 1

Source: How to delete duplicate records in SQL Server

score 0 · Answer 3 · answered Jan 30 '19 at 18:24

Is there a reason why you need to do an insert then delete? If you're trying to insert -> delete, it sounds like you're actually trying to update the data by Part_ID.

update  InvoiceLine a

set     More_Info = b.Description       

from (  select  Part_ID,
                Description
        from    Test_InvoiceLine    ) b

where   b.Part_ID = a.Part_ID

This will update More_Info in the InvoiceLine table with Description.

How do you create a delete query that deletes rows with criteria?

3 Answers3