?
Solved

sql server delete sql

Posted on 2014-07-28
18
Medium Priority
?
200 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
  • +2
18 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 1400 total points
ID: 40225634
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
ID: 40225649
Thanks
Don't I have to use VERSION along with ID in partition clause?
0
 
LVL 25

Expert Comment

by:chaau
ID: 40225657
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

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

Expert Comment

by:chaau
ID: 40225676
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
ID: 40225892
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 600 total points
ID: 40225916
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 25

Expert Comment

by:chaau
ID: 40225926
@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 25

Expert Comment

by:chaau
ID: 40225937
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
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 40225961
@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
ID: 40225969
@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 15

Expert Comment

by:Vikas Garg
ID: 40226169
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
ID: 40226500
@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 15

Expert Comment

by:Vikas Garg
ID: 40226506
@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
ID: 40227798
@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:Scott Pletcher
ID: 40228040
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
ID: 40228378
Thanks for the clarification @ScottPletcher

--itsvtk
0
 

Author Comment

by:royjayd
ID: 40236461
thanks all for the answers.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

762 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