The following warnings occurred:
Warning [2] Cannot modify header information - headers already sent by (output started at D:\inetpub\vhosts\visionwebdirectory.com\httpdocs\forum\inc\scripts.php:2) - Line: 1701 - File: D:\inetpub\vhosts\visionwebdirectory.com\httpdocs\forum\inc\functions.php PHP 5.2.17 (WINNT)
File Line Function
[PHP]   errorHandler->error
D:\inetpub\vhosts\visionwebdirectory.com\httpdocs\forum\inc\functions.php 1701 header
D:\inetpub\vhosts\visionwebdirectory.com\httpdocs\forum\inc\functions.php 1763 my_setcookie
D:\inetpub\vhosts\visionwebdirectory.com\httpdocs\forum\inc\functions_indicators.php 42 my_set_array_cookie
D:\inetpub\vhosts\visionwebdirectory.com\httpdocs\forum\showthread.php 578 mark_thread_read
Warning [2] Cannot modify header information - headers already sent by (output started at D:\inetpub\vhosts\visionwebdirectory.com\httpdocs\forum\inc\scripts.php:2) - Line: 1701 - File: D:\inetpub\vhosts\visionwebdirectory.com\httpdocs\forum\inc\functions.php PHP 5.2.17 (WINNT)
File Line Function
[PHP]   errorHandler->error
D:\inetpub\vhosts\visionwebdirectory.com\httpdocs\forum\inc\functions.php 1701 header
D:\inetpub\vhosts\visionwebdirectory.com\httpdocs\forum\inc\functions.php 1763 my_setcookie
D:\inetpub\vhosts\visionwebdirectory.com\httpdocs\forum\inc\functions_indicators.php 218 my_set_array_cookie
D:\inetpub\vhosts\visionwebdirectory.com\httpdocs\forum\inc\functions_indicators.php 48 mark_forum_read
D:\inetpub\vhosts\visionwebdirectory.com\httpdocs\forum\showthread.php 578 mark_thread_read





Post Reply 
 
Thread Rating:
  • 0 Votes - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Delete Duplicate records
06-30-2013, 07:11 PM
Post: #1
Delete Duplicate records
Here we go with the deletion of all duplicate rows from a data table. We do not have any primary on this table.

Please note the important role of 'PARTITION BY' keyword. This keyword make partition on the bases of specified columns. While the ROW_NUMBER() function will give numbering to all records.

We collects the new record structure in alias 'CTE' with the help of keyword 'WITH'.
After this we deletes the records with 'DuplicateCount' grater than 1 :


/* Delete Duplicate records */

WITH CTE (COl1,Col2, DuplicateCount)

AS

(

SELECT COl1,Col2,

ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount

FROM DuplicateRcordTable

)

DELETE

FROM CTE

WHERE DuplicateCount > 1

GO
Find all posts by this user
Quote this message in a reply
Post Reply 


Forum Jump:


User(s) browsing this thread: 1 Guest(s)