Solved

sql server delete sql

Posted on 2014-07-28
18
191 Views
Last Modified: 2014-08-02
hi guys

I have my Table customer with data like
count   ID      VERSION       TYPE       
1        126      4           SHOE
3        126      4           SHOE
4        127      6           SHOE
5        127      6           SHOE
6        128      7           SHOE
7        128      7           SHOE
9        128      7           SHOE

I am trying to come up with a sql which will check if ID and VERSION for two or more rows are same for the TYPE = SHOE, then delete the row(s)
with higher count and keep only one row with lower count.
So finally my result set should look like
count   ID        VERSION      TYPE       
1        126      4           SHOE
4        127      6           SHOE
6        128      7           SHOE

trying to come up with the sql but stuck..

delete from customer where TYPE = 'SHOE' and
..

any help will be greatly appreciated.
Thanks.
0
Comment
Question by:royjayd
  • 6
  • 5
  • 4
  • +2
18 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 350 total points
Comment Utility
The easiest way to accomplish this is by using a window function ROW_NUMBER:
;with dup as (
SELECT *,
  ROW_NUMBER() OVER (PARTITION BY [TYPE], [ID] ORDER BY [count]) rn
  FROM Table1
  WHERE [TYPE] = 'SHOE')
DELETE FROM dup where rn > 1;

Open in new window

0
 

Author Comment

by:royjayd
Comment Utility
Thanks
Don't I have to use VERSION along with ID in partition clause?
0
 
LVL 24

Expert Comment

by:chaau
Comment Utility
You are right, I have missed it. Here is the correct query:
;with dup as (
SELECT *,
  ROW_NUMBER() OVER (PARTITION BY [TYPE], [ID], [VERSION] ORDER BY [count]) rn
  FROM Table1
  WHERE [TYPE] = 'SHOE')
DELETE FROM dup where rn > 1;

Open in new window

0
 

Author Comment

by:royjayd
Comment Utility
I think I got it. You are assigning a rank and deleting ranks >1
Is therr any other way.
Thanks.
0
 
LVL 24

Expert Comment

by:chaau
Comment Utility
Yes, there are other ways too, but this one is the easiest (and fastest in my opinion). Do you wish to explore them?
0
 

Author Comment

by:royjayd
Comment Utility
yeah, I was curious to know if we can do it using one single delete statement  instead of a Select ..Delete

Thanks.
0
 
LVL 14

Assisted Solution

by:Thandava Vallepalli
Thandava Vallepalli earned 150 total points
Comment Utility
Here is the single delete statement.

============================================

delete t1
from TabVer t1
where Cnt > ( select min(Cnt)
                        from TabVer t2
                        where t1.[ID] = t2.[ID]
                                    and t1.[version] = t2.[version]
                                    and t1.[type] = t2.[type]
                  );

==================================


Output:
=========================

Cnt         ID          version     type
----------- ----------- ----------- ----------------------------------------------------------------------------------------------------
1           126         4           SHOE
4           127         6           SHOE
6           128         7           SHOE


--itsvtk
0
 
LVL 24

Expert Comment

by:chaau
Comment Utility
@Thandava Vallepalli: Your statement will not work for the cases with duplicate count values. I.e. if there are two or more records with count=4 for ID=126, they all will be left and not deleted
0
 
LVL 24

Expert Comment

by:chaau
Comment Utility
There is no way in my opinion to delete is using a single delete. There must be a select. However, you will be able to insert records to a temp table using this statement:
select [ID], [version], [type], min(Cnt) Cnt
INTO #Temp
from TabVer
group by [ID], [version], [type]

Open in new window

then truncate the original table and re-insert the records from the temp table.
TRUNCATE TabVer
INSERT INTO TabVer ([ID], [version], [type], Cnt)
SELECT [ID], [version], [type], Cnt
FROM #Temp

Open in new window

0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 14

Expert Comment

by:Thandava Vallepalli
Comment Utility
@Chaau,

Generally most of the tables in RDMBS contain primary key.  I think, [Cnt] is a primary key.
If [Cnt] is not primary key, here is another single delete statement which takes care of duplicate [Cnt] as well.

=========================================
delete t1
from TabVer t1
            inner join (
                                    select row_number() over (partition by [ID], [version], [type] order by [Cnt]) as row_id
                                                ,[Cnt]
                                                ,[ID]
                                                ,[Version]
                                                ,[Type]
                                    from TabVer t2
                                    where t2.[Type] = 'SHOE'
                              ) t3
                        on t1.cnt = t3.cnt
                              and t1.[id] = t3.[id]
                              and t1.[version] = t3.[version]
                              and t1.[type] = t3.[type]
where t1.[Type] = 'SHOE'
            and row_id > 1;
======================================

--itsvtk
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
Comment Utility
@Chaau,

Quick advice....Never ever use truncate table in production database.  
In case of mistakes in queires, we will loose entire data and can't rollback it.


--itsvtk
0
 
LVL 14

Expert Comment

by:Vikas Garg
Comment Utility
Hi,

You can try this ..

; WITH CTE AS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY id,version ORDER BY counts,id) CTN FROM  [dbo].[T3323]
) 
--SELECT *  FROM CTE WHERE CTN > 1
DELETE FROM CTE WHERE CTN > 1

Open in new window


And we can't write direct delete without select since
Windowed functions can only appear in the SELECT or ORDER BY clauses
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
Comment Utility
@Vikas,

You can write single delete statement (without using window funciton) to delete duplicates from a table, if you have a primary key in that table (I think, [Cnt] is primary key).  Please check my first answer above for single delete statement.

If you don't have primary key in a table, you can still delete duplicate records using window function in a single delete statement. Please check my second answer above.


--itsvtk
0
 
LVL 14

Expert Comment

by:Vikas Garg
Comment Utility
@Thandava,

Thanks for you valuable comment but in your second example is the Window function is used with Select Statement
(
select row_number() over (partition by [ID], [version], [type] order by [Cnt]) as row_id
                                                ,[Cnt]
)

and that is what my comment says that without Select you can't use Windows Function..

Thanks again......
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
Comment Utility
@Vikas,

The select statement with window function is required only if a table doesn't have a primary key.  
Most of the cases, in RDBDS, every table contains a primary key / composite primary key.

I assumed that [Cnt] is the primary key column in customer table. If my assumption is correct, then no need of window function.

Here is one statement without any window function to delete duplicates.

==============================================================
DELETE t1
FROM TabVer t1
WHERE Cnt > ( SELECT min(Cnt)
                        FROM TabVer t2
                        WHERE t1.[ID] = t2.[ID]
                                    AND t1.[version] = t2.[version]
                                    AND t1.[type] = t2.[type]
                  )
            AND t1.[Type] = 'SHOE';
=======================================================



If [Cnt] is not a primary key, then have to use window function. But still it is a single statement which deletes duplicates, bit complicated though.
=======================================================
DELETE t1
FROM TabVer t1
            INNER JOIN (
                                    SELECT ROW_NUMBER() OVER (PARTITION BY [ID], [version], [type] ORDER BY [Cnt]) AS row_id
                                                ,[Cnt]
                                                ,[ID]
                                                ,[Version]
                                                ,[Type]
                                    FROM TabVer t2
                                    WHERE t2.[Type] = 'SHOE'
                              ) t3
                        ON t1.cnt = t3.cnt
                              AND t1.[id] = t3.[id]
                              AND t1.[version] = t3.[version]
                              AND t1.[type] = t3.[type]
WHERE t1.[Type] = 'SHOE'
            and row_id > 1;
=======================================================


--itsvtk
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
If count can have duplicates, you must use a Windowing function to do this with any efficiency at all.


Btw:
>> Never ever use truncate table in production database.  
In case of mistakes in queries, we will lose entire data and can't rollback it. <<

FALSE: Truncate CAN be rolled back.
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
Comment Utility
Thanks for the clarification @ScottPletcher

--itsvtk
0
 

Author Comment

by:royjayd
Comment Utility
thanks all for the answers.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now