Databases
StackOverflow2013
Postsdbo
POTrouble deleting duplicate records using partition and rank() in sql server
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTrouble deleting duplicate records using partition and rank() in sql server
    primarykey
    data
    text
    <p>I am trying to identify duplicate records and then delete one of the duplicate record using PARTITION and RANK() n SQL Server 2008. I condition to delete duplicate record is that it should not be referenced in another table. </p> <p>I have a Language table that has some duplicate languages. Employee table has employees and mapping to language. I have to delete one of the duplicate records if that Language id is not being mapped in Employee table.</p> <pre><code>CREATE TABLE MY_LANGUAGE (LANGUAGEID INT, LANGUAGENAME VARCHAR(20)) CREATE TABLE MY_EMPLOYEE (EMPID INT, NAME VARCHAR(20), LANGUAGEID INT) INSERT INTO MY_LANGUAGE VALUES(1, 'ENGLISH') INSERT INTO MY_LANGUAGE VALUES(2, 'FRENCH') INSERT INTO MY_LANGUAGE VALUES(3, 'ITALIAN') INSERT INTO MY_LANGUAGE VALUES(4, 'GERMAN') INSERT INTO MY_LANGUAGE VALUES(5, 'ITALIAN') INSERT INTO MY_LANGUAGE VALUES(6, 'GERMAN') INSERT INTO MY_LANGUAGE VALUES(7, 'SPANISH') INSERT INTO MY_EMPLOYEE VALUES (10, 'GLEN', 1) INSERT INTO MY_EMPLOYEE VALUES (20, 'PETER', 2) INSERT INTO MY_EMPLOYEE VALUES (30, 'MARIA', 3) </code></pre> <p>If you see, I have two languages that are duplicate and one of them is being used by an employee. I want to delete language ids 4 and 5.</p> <pre><code>LANGUAGENAME LANGUAGEID EMPNAME GERMAN 4 GERMAN 6 ITALIAN 3 MARIA ITALIAN 5 </code></pre> <p>I have tried to create a select statement to return what I want to delete:</p> <pre><code>WITH CTE AS ( SELECT L.LANGUAGENAME, L.LANGUAGEID, RANK() OVER(PARTITION BY L.LANGUAGENAME ORDER BY L.LANGUAGEID) AS RANKING FROM MY_LANGUAGE L INNER JOIN ( SELECT LANGUAGENAME, COUNT(*) AS DUPECOUNT FROM MY_LANGUAGE GROUP BY LANGUAGENAME HAVING COUNT(*) &gt; 1 ) LC ON L.LANGUAGENAME = LC.LANGUAGENAME WHERE NOT EXISTS (SELECT 1 FROM MY_EMPLOYEE WHERE MY_EMPLOYEE.LANGUAGEID = L.LANGUAGEID)) SELECT * FROM CTE WHERE RANKING = 1 </code></pre> <p>This return the following</p> <pre><code>LANGUAGENAME LANGUAGEID RANKING GERMAN 4 1 ITALIAN 5 1 </code></pre> <p>When I try to delete I get an error:</p> <pre><code>WITH CTE AS ( SELECT L.LANGUAGENAME, L.LANGUAGEID, RANK() OVER(PARTITION BY L.LANGUAGENAME ORDER BY L.LANGUAGEID) AS RANKING FROM MY_LANGUAGE L INNER JOIN ( SELECT LANGUAGENAME, COUNT(*) AS DUPECOUNT FROM MY_LANGUAGE GROUP BY LANGUAGENAME HAVING COUNT(*) &gt; 1 ) LC ON L.LANGUAGENAME = LC.LANGUAGENAME WHERE NOT EXISTS (SELECT 1 FROM MY_EMPLOYEE WHERE MY_EMPLOYEE.LANGUAGEID = L.LANGUAGEID)) DELETE FROM CTE WHERE RANKING = 1 </code></pre> <p>Error that I get is:</p> <blockquote> <p>Msg 4405, Level 16, State 1, Line 1<br> View or function 'CTE' is not updatable because the modification affects multiple base tables.</p> </blockquote> <p>Any ideas how to fix this or may be it can be simplified. Thanks to @Szymon for showing a temp table solution but I am hoping to get a solution without temp tables (if possible).</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSri Reddy
    1. USSri Reddy
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POTrouble deleting duplicate records using partition and rank() in sql server
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload