CODEWITHSUNDEEP
sqlsql-server
Kliver Max
Kliver Max

Reputation: 5299

How can i remove rows from several tables with SQL?

I have a 3 tables:

mainTable:
id | uniqField
--------------
1  | 1111
2  | 1111
3  | 2222
4  | 2222
5  | 3333
6  | 4444

table1:
id|name|deleted
----------
 1|Mike|0
 2|Mike|0
 5|John|0

table2:
id|name|deleted
----------
 3|Peke|0
 4|Peke|0
 6|Vels|0

That tables bind by id field.
Now i want to remove duplicates from this tables. For mainTable i can use:

DELETE mainTable
FROM mainTable
LEFT OUTER JOIN (
   SELECT MIN(id) as RowId, uniqField
   FROM mainTable 
   GROUP BY uniqField
) as KeepRows ON
   mainTable.id= KeepRows.RowId
WHERE
   KeepRows.RowId IS NULL

But in tables1 and table2 i want to set deleted field to 0->1 to duplicates I mean:

1  | 1111 
2  | 1111  -> duplicate->remove

 1|Mike|0
 2|Mike|0->duplicate-> 0->1

Upvotes: 2

Views: 67

Answers (1)

Devart
Devart

Reputation: 121902

Try this one -

SET NOCOUNT ON;

DECLARE @mainTable TABLE
(
    id INT,
    uniqField INT
)

INSERT INTO @mainTable (id, uniqField) 
VALUES (1, 1111), (2, 1111), (3, 2222), (4, 2222), (5, 3333), (6, 4444)

DECLARE @deleted TABLE (id INT)

;WITH cte AS 
(
    SELECT *, RowNum = ROW_NUMBER() OVER (PARTITION BY uniqField ORDER BY 1/0)
    FROM @mainTable
)
DELETE FROM cte
OUTPUT DELETED.id INTO @deleted
WHERE RowNum > 1

DECLARE @table1 TABLE
(
    id INT,
    uniqField VARCHAR(10),
    deleted BIT
)

DECLARE @table2 TABLE
(
    id INT,
    uniqField VARCHAR(10),
    deleted BIT
)

INSERT INTO @table1 (id, uniqField, deleted) 
VALUES (1, 'Mike', 0), (2, 'Mike', 0), (5, 'John', 0)

INSERT INTO @table2 (id, uniqField, deleted) 
VALUES (3, 'Peke', 0), (4, 'Peke', 0), (6, 'Vels', 0)

UPDATE @table1
SET deleted = 1
FROM @deleted
WHERE [@deleted].id = [@table1].id

UPDATE @table2
SET deleted = 1
FROM @deleted
WHERE [@deleted].id = [@table2].id

SELECT * FROM @table1
SELECT * FROM @table2

Output -

id          uniqField  deleted
----------- ---------- -------
1           Mike       0
2           Mike       1
5           John       0

id          uniqField  deleted
----------- ---------- -------
3           Peke       0
4           Peke       1
6           Vels       0

Upvotes: 1

Related Questions