How to delete rows that has multiple values and pick only 1 row

Question

Please let me know if there's a query for this case: I want to delete multiple rows based on spesific column

id    | date       |   user    | item  |      material      | Text | Description |
------+------------+-----------+-------+--------------------+------+-------------+
12345 | 31.03.2015 | Starbucks | 00010 | 000000001011000106 | abcd |  something  | 
null  | 31.03.2015 | Starbucks | 00010 | 000000001011000106 | abcd |  something  | 
54321 | 31.03.2015 |   Burger  | 00010 | 000000001011000106 | abcd |  something  |
11111 | 31.03.2015 |   Burger  | 00010 | 000000001011000106 | abcd |  something  |

Let say I want to delete multiple rows that contains user "Starbucks" more than 1, I want to delete the one that has id null. But if there's no id that contains null, like "Burger", I just want to delete one, and pick 1 whatever it is. So, it should be like this :

id    | date       |   user    | item  |      material      | Text | Description |
------+------------+-----------+-------+--------------------+------+-------------+
12345 | 31.03.2015 | Starbucks | 00010 | 000000001011000106 | abcd |  something  | 
54321 | 31.03.2015 |   Burger  | 00010 | 000000001011000106 | abcd |  something  |

Can I?

there's possibility that id not always be numeric, but character, it is a person who input this not a system. So, i would say probably.. — Bima Ardi, May 21 '15 at 02:32

score 3 · Accepted Answer · answered May 21 '15 at 02:25

3

Yes you can.

You need to group the results to keep and then use that table with a join to connect to the master table. Here's one way to only keep the records with the max id and no null ids

Delete from [Table]
From [Table]
left join (
    Select MAX(ID) as ID
    from [Table]
    Where ID is not Null
    group by [USER],item,[date],material,[TEXT],Description
    -- Results to Keep
) as P on [Table].ID=P.ID
where P.ID is null

answered May 21 '15 at 02:25

Middletone

4,190
12
53
74

what if I have the data that contains same id and same user.. every column contains same.. will it work? But thanks anyway, I will try it.. – Bima Ardi May 21 '15 at 02:39
You should always have at least one unique column. If you don't, just add one arbitrarily and use that to match on for the max value instead of ID. – Middletone May 21 '15 at 02:41

score 0 · Answer 2 · answered May 21 '15 at 03:43

You can also use Window functions within a CTE

Sample data

DECLARE @tbl TABLE (id INT, [date] VARCHAR(10) ,[user] VARCHAR(20), item VARCHAR(10), material VARCHAR(50), [Text] VARCHAR(10), [Description] VARCHAR(20))

INSERT @tbl
SELECT 12345, '1.03.2015', 'Starbucks', '00010', '000000001011000106', 'abcd', 'something' UNION
SELECT null, '31.03.2015', 'Starbucks', '00010', '000000001011000106', 'abcd', 'something' UNION
SELECT 54321, '31.03.2015', 'Burger', '00010', '000000001011000106', 'abcd', 'something' UNION
SELECT 11111, '31.03.2015', 'Burger', '00010', '000000001011000106', 'abcd', 'something'

Query

;WITH C AS(
    SELECT  ROW_NUMBER() OVER(PARTITION BY [user] ORDER BY [user]) AS Rn
            ,Id
    FROM @tbl
    WHERE id IS NOT NULL    
)
DELETE FROM @tbl WHERE Id IN(
    SELECT  Id
    FROM C
    WHERE Rn != 1
    AND id IS NOT NULL)
OR Id IS NULL

Output

SELECT * FROM @tbl

id      date        user       item     material            Text    Description
--------------------------------------------------------------------------------
11111   31.03.2015  Burger     00010    000000001011000106  abcd    something
12345   1.03.2015   Starbucks  00010    000000001011000106  abcd    something

Indra Prakash Tiwari · Answer 3 · 2015-05-21T04:31:58.467

0

This Could help

--Sample Data

Create TABLE ABC (id INT, [date] VARCHAR(10) ,[user] VARCHAR(20), item VARCHAR(10), material VARCHAR(50), [Text] VARCHAR(10), [Description] VARCHAR(20))

INSERT ABC
SELECT 12345, '1.03.2015', 'Starbucks', '00010', '000000001011000106', 'abcd', 'something' UNION
SELECT null, '31.03.2015', 'Starbucks', '00010', '000000001011000106', 'abcd', 'something' UNION
SELECT 54321, '31.03.2015', 'Burger', '00010', '000000001011000106', 'abcd', 'something' UNION
SELECT 11111, '31.03.2015', 'Burger', '00010', '000000001011000106', 'abcd', 'something'

-- result

select * from ABC

-- Query

; With CTE AS
(SELECT *, ROW_NUMBER() over(partition by [user] order by ID desc) row_id from ABC 
) DELETE from CTE where row_id >1

--final Result

select * from ABC

edited May 21 '15 at 04:31

answered May 21 '15 at 04:25

Indra Prakash Tiwari

947
1
6
15

I have this kind of error, "OLE DB provider "SQLNCLI" for linked server "10.80.1.7" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." what does it mean? – Bima Ardi May 21 '15 at 13:37
Hi @BimaArdi Can you please send me your actual query which you run and getting above error? – Indra Prakash Tiwari May 21 '15 at 13:45

How to delete rows that has multiple values and pick only 1 row

3 Answers3