Filtering & deleting recurring data in table

I have two tables in SQL, One contains grand total per line item (master table) and another that contains selling price & item detail per line item (Sub Table). The hopes to join both tables together but yet only return total sum price based on each product and its price. The query would delete any recurring data in the master table

CodePudding user response：

-- create table
CREATE TABLE TEST_DB.dbo.MasterTable (
    id int IDENTITY(0,1) NOT NULL,
    ShipmentId int NULL,
    TotalPrice bigint NULL,
    CONSTRAINT MasterTable_PK PRIMARY KEY (id)
);

-- insert sample data 
INSERT INTO TEST_DB.dbo.MasterTable (ShipmentId, TotalPrice) VALUES(5758, 2250);
INSERT INTO TEST_DB.dbo.MasterTable (ShipmentId, TotalPrice) VALUES(5758, 2250);
INSERT INTO TEST_DB.dbo.MasterTable (ShipmentId, TotalPrice) VALUES(5758, 2250);
INSERT INTO TEST_DB.dbo.MasterTable (ShipmentId, TotalPrice) VALUES(5758, 2250);
INSERT INTO TEST_DB.dbo.MasterTable (ShipmentId, TotalPrice) VALUES(5758, 2250);
INSERT INTO TEST_DB.dbo.MasterTable (ShipmentId, TotalPrice) VALUES(5758, 2250);
INSERT INTO TEST_DB.dbo.MasterTable (ShipmentId, TotalPrice) VALUES(5758, 2250);
INSERT INTO TEST_DB.dbo.MasterTable (ShipmentId, TotalPrice) VALUES(5758, 2250);
INSERT INTO TEST_DB.dbo.MasterTable (ShipmentId, TotalPrice) VALUES(5758, 2250);
INSERT INTO TEST_DB.dbo.MasterTable (ShipmentId, TotalPrice) VALUES(5758, 2250);
INSERT INTO TEST_DB.dbo.MasterTable (ShipmentId, TotalPrice) VALUES(5758, 0);
INSERT INTO TEST_DB.dbo.MasterTable (ShipmentId, TotalPrice) VALUES(5758, 16500);
INSERT INTO TEST_DB.dbo.MasterTable (ShipmentId, TotalPrice) VALUES(5758, 16500);
INSERT INTO TEST_DB.dbo.MasterTable (ShipmentId, TotalPrice) VALUES( 5758, 16500);
INSERT INTO TEST_DB.dbo.MasterTable (ShipmentId, TotalPrice) VALUES(5758, 0);
INSERT INTO TEST_DB.dbo.MasterTable (ShipmentId, TotalPrice) VALUES(5758, 6000);
INSERT INTO TEST_DB.dbo.MasterTable (ShipmentId, TotalPrice) VALUES(5758, 6000);
INSERT INTO TEST_DB.dbo.MasterTable (ShipmentId, TotalPrice) VALUES(5758, 6000);
INSERT INTO TEST_DB.dbo.MasterTable (ShipmentId, TotalPrice) VALUES(5758, 0);



-- deleting all dublicates 
declare 
@del_count integer; 

set @del_count = 1; 

while @del_count > 0
begin
    
    delete from TEST_DB.dbo.MasterTable 
    where id in 
    (
        select min(id) from TEST_DB.dbo.MasterTable
        group by ShipmentId, TotalPrice
        having count(*) > 1
    );
    set @del_count = @@ROWCOUNT;
end

CodePudding user response：

You can use an update-able CTE for this

WITH cte AS (
    SELECT *,
      rn = ROW_NUMBER OVER (PARTITION BY ShipmentId, TotalPrice ORDER BY id)
    FROM MasterTable
)
DELETE cte
WHERE rn > 1;

CodePudding user response：

Provided the what i have tried query you supplied is the right way to spot what to delete, presumably first row is equal any any later gets an increment or something?

delete from Master 
where Id in (
    select t.Id
    from Master as t 
    inner join subtableas u 
    on u.sequence = t.sequence 
    where t.sequence <> u.rrank 
)