TechCodeview

I det här avsnittet lär vi oss olika sätt att ta bort dubbletter av rader i MySQL och Oracle . Om SQL tabell innehåller dubblettrader, då måste vi ta bort dubblettraderna.

Förbereder provdata

Skriptet skapar tabellen med namnet kontakter .

javascript onload script

 DROP TABLE IF EXISTS contacts; CREATE TABLE contacts ( id INT PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(30) NOT NULL, last_name VARCHAR(25) NOT NULL, email VARCHAR(210) NOT NULL, age VARCHAR(22) NOT NULL ); 

I tabellen ovan har vi infogat följande data.

 INSERT INTO contacts (first_name,last_name,email,age) VALUES ('Kavin','Peterson','[email protected]','21'), ('Nick','Jonas','[email protected]','18'), ('Peter','Heaven','[email protected]','23'), ('Michal','Jackson','[email protected]','22'), ('Sean','Bean','[email protected]','23'), ('Tom ','Baker','[email protected]','20'), ('Ben','Barnes','[email protected]','17'), ('Mischa ','Barton','[email protected]','18'), ('Sean','Bean','[email protected]','16'), ('Eliza','Bennett','[email protected]','25'), ('Michal','Krane','[email protected]','25'), ('Peter','Heaven','[email protected]','20'), ('Brian','Blessed','[email protected]','20'); ('Kavin','Peterson','[email protected]','30'), 

Vi kör skriptet för att återskapa testdata efter att ha kört en RADERA påstående .

Frågan returnerar data från kontakttabellen:

 SELECT * FROM contacts ORDER BY email; 

Följande SQL-fråga returnerar dubblettmeddelanden från kontakttabellen:

 SELECT email, COUNT(email) FROM contacts GROUP BY email HAVING COUNT (email) > 1; 

Vi har tre rader med duplicera e-postmeddelanden.

(A) Ta bort dubbletter av rader med DELETE JOIN-satsen

 DELETE t1 FROM contacts t1 INNERJOIN contacts t2 WHERE t1.id <t2.id and t1.email="t2.email;" < pre> <p> <strong>Output:</strong> </p> <pre> Query OK, three rows affected (0.10 sec) </pre> <p>Three rows had been deleted. We execute the query, given below to finds the <strong>duplicate emails</strong> from the table.</p> <pre> SELECT email, COUNT (email) FROM contacts GROUP BY email HAVING COUNT (email) &gt; 1; </pre> <p>The query returns the empty set. To verify the data from the contacts table, execute the following SQL query:</p> <pre> SELECT * FROM contacts; </pre> <br> <table class="table"> <tr> <td>id</td> <td>first_name</td> <td>last_name</td> <td>Email</td> <td>age</td> </tr> <tr> <td>7</td> <td>Ben</td> <td>Barnes</td> <td> [email protected] </td> <td>21</td> </tr> <tr> <td>13</td> <td>Brian</td> <td>Blessed</td> <td> [email protected] </td> <td>18</td> </tr> <tr> <td>10</td> <td>Eliza</td> <td>Bennett</td> <td> [email protected] </td> <td>23</td> </tr> <tr> <td>1</td> <td>Kavin</td> <td>Peterson</td> <td> [email protected] </td> <td>22</td> </tr> <tr> <td>8</td> <td>Mischa</td> <td>Barton</td> <td> [email protected] </td> <td>20</td> </tr> <tr> <td>11</td> <td>Micha</td> <td>Krane</td> <td> [email protected] </td> <td>17</td> </tr> <tr> <td>4</td> <td>Michal</td> <td>Jackson</td> <td> [email protected] </td> <td>18</td> </tr> <tr> <td>2</td> <td>Nick</td> <td>Jonas</td> <td> [email protected] </td> <td>16</td> </tr> <tr> <td>3</td> <td>Peter</td> <td>Heaven</td> <td> [email protected] </td> <td>25</td> </tr> <tr> <td>5</td> <td>Sean</td> <td>Bean</td> <td> [email protected] </td> <td>20</td> </tr> <tr> <td>6</td> <td>Tom</td> <td>Baker</td> <td> [email protected] </td> <td>30</td> </tr> </table> <p>The rows <strong>id&apos;s 9, 12, and 14</strong> have been deleted. We use the below statement to delete the duplicate rows:</p> <p>Execute the script for <strong>creating</strong> the contact.</p> <pre> DELETE c1 FROM contacts c1 INNERJ OIN contacts c2 WHERE c1.id &gt; c2.id AND c1.email = c2.email; </pre> <br> <table class="table"> <tr> <td>id</td> <td>first_name</td> <td>last_name</td> <td>email</td> <td>age</td> </tr> <tr> <td>1</td> <td>Ben</td> <td>Barnes</td> <td> [email protected] </td> <td>21</td> </tr> <tr> <td>2</td> <td> <strong>Kavin</strong> </td> <td> <strong>Peterson</strong></td> <td> <strong> [email protected] </strong> </td> <td> <strong>22</strong> </td> </tr> <tr> <td>3</td> <td>Brian</td> <td>Blessed</td> <td> [email protected] </td> <td>18</td> </tr> <tr> <td>4</td> <td>Nick</td> <td>Jonas</td> <td> [email protected] </td> <td>16</td> </tr> <tr> <td>5</td> <td>Michal</td> <td>Krane</td> <td> [email protected] </td> <td>17</td> </tr> <tr> <td>6</td> <td>Eliza</td> <td>Bennett</td> <td> [email protected] </td> <td>23</td> </tr> <tr> <td>7</td> <td>Michal</td> <td>Jackson</td> <td> [email protected] </td> <td>18</td> </tr> <tr> <td>8</td> <td> <strong>Sean</strong> </td> <td> <strong>Bean</strong> </td> <td> <strong> [email protected] </strong> </td> <td> <strong>20</strong> </td> </tr> <tr> <td>9</td> <td>Mischa</td> <td>Barton</td> <td> [email protected] </td> <td>20</td> </tr> <tr> <td>10</td> <td> <strong>Peter</strong> </td> <td> <strong>Heaven</strong> </td> <td> <strong> [email protected] </strong> </td> <td> <strong>25</strong> </td> </tr> <tr> <td>11</td> <td>Tom</td> <td>Baker</td> <td> [email protected] </td> <td>30</td> </tr> </table> <h2>(B) Delete duplicate rows using an intermediate table</h2> <p>To delete a duplicate row by using the intermediate table, follow the steps given below:</p> <p> <strong>Step 1</strong> . Create a new table <strong>structure</strong> , same as the real table:</p> <pre> CREATE TABLE source_copy LIKE source; </pre> <p> <strong>Step 2</strong> . Insert the distinct rows from the original schedule of the database:</p> <pre> INSERT INTO source_copy SELECT * FROM source GROUP BY col; </pre> <p> <strong>Step 3</strong> . Drop the original table and rename the immediate table to the original one.</p> <pre> DROP TABLE source; ALTER TABLE source_copy RENAME TO source; </pre> <p>For example, the following statements delete the <strong>rows</strong> with <strong>duplicate</strong> emails from the contacts table:</p> <pre> -- step 1 CREATE TABLE contacts_temp LIKE contacts; -- step 2 INSERT INTO contacts_temp SELECT * FROM contacts GROUP BY email; -- step 3 DROP TABLE contacts; ALTER TABLE contacts_temp RENAME TO contacts; </pre> <h2>(C) Delete duplicate rows using the ROW_NUMBER() Function</h2> <h4>Note: The ROW_NUMBER() function has been supported since MySQL version 8.02, so we should check our MySQL version before using the function.</h4> <p>The following statement uses the <strong>ROW_NUMBER ()</strong> to assign a sequential integer to every row. If the email is duplicate, the row will higher than one.</p> <pre> SELECT id, email, ROW_NUMBER() OVER (PARTITION BY email ORDER BY email ) AS row_num FROM contacts; </pre> <p>The following SQL query returns <strong>id list</strong> of the duplicate rows:</p> <pre> SELECT id FROM (SELECT id, ROW_NUMBER() OVER ( PARTITION BY email ORDER BY email) AS row_num FROM contacts ) t WHERE row_num&gt; 1; </pre> <p> <strong>Output:</strong> </p> <table class="table"> <tr> <td>id</td> </tr> <tr> <td>9</td> </tr> <tr> <td>12</td> </tr> <tr> <td>14</td> </tr> </table> <h2>Delete Duplicate Records in Oracle</h2> <p>When we found the duplicate records in the table, we had to delete the unwanted copies to keep our data clean and unique. If a table has duplicate rows, we can delete it by using the <strong>DELETE</strong> statement.</p> <p>In the case, we have a column, which is not the part of <strong>group</strong> used to <strong>evaluate</strong> the <strong>duplicate</strong> records in the table.</p> <p>Consider the table given below:</p> <table class="table"> <tr> <td>VEGETABLE_ID</td> <td>VEGETABLE_NAME</td> <td>COLOR</td> </tr> <tr> <td>01</td> <td>Potato</td> <td>Brown</td> </tr> <tr> <td>02</td> <td>Potato</td> <td>Brown</td> </tr> <tr> <td>03</td> <td>Onion</td> <td>Red</td> </tr> <tr> <td>04</td> <td>Onion</td> <td>Red</td> </tr> <tr> <td>05</td> <td>Onion</td> <td>Red</td> </tr> <tr> <td>06</td> <td>Pumpkin</td> <td>Green</td> </tr> <tr> <td>07</td> <td>Pumpkin</td> <td>Yellow</td> </tr> </table> <br> <pre> -- create the vegetable table CREATE TABLE vegetables ( VEGETABLE_ID NUMBER generated BY DEFAULT AS ID ENTITY, VEGETABLE_NAME VARCHAR2(100), color VARCHAR2(20), PRIMARY KEY (VEGETABLE_ID) ); </pre> <br> <pre> -- insert sample rows INSERT INTO vegetables (VEGETABLE_NAME,color) VALUES(&apos;Potato&apos;,&apos;Brown&apos;); INSERT INTO vegetables (VEGETABLE_NAME,color) VALUES(&apos;Potato&apos;,&apos;Brown&apos;); INSERT INTO vegetables (VEGETABLE_NAME,color) VALUES(&apos;Onion&apos;,&apos;Red&apos;); INSERT INTO vegetables (VEGETABLE_NAME,color) VALUES(&apos;Onion&apos;,&apos;Red&apos;); INSERT INTO vegetables (VEGETABLE_NAME,color) VALUES(&apos;Onion&apos;,&apos;Red&apos;); INSERT INTO vegetables (VEGETABLE_NAME,color) VALUES(&apos;Pumpkin&apos;,&apos;Green&apos;); INSERT INTO vegetables (VEGETABLE_NAME,color) VALUES(&apos;Pumpkin&apos;,&apos;Yellow&apos;); </pre> <br> <pre> -- query data from the vegetable table SELECT * FROM vegetables; </pre> <p>Suppose, we want to keep the row with the highest <strong>VEGETABLE_ID</strong> and delete all other copies.</p> <pre> SELECT MAX (VEGETABLE_ID) FROM vegetables GROUP BY VEGETABLE_NAME, color ORDER BY MAX(VEGETABLE_ID); </pre> <br> <table class="table"> <tr> <td>MAX(VEGETABLE_ID)</td> </tr> <tr> <td>2</td> </tr> <tr> <td>5</td> </tr> <tr> <td>6</td> </tr> <tr> <td>7</td> </tr> </table> <p>We use the <strong>DELETE</strong> statement to delete the rows whose values in the <strong>VEGETABLE_ID COLUMN</strong> are not the <strong>highest</strong> .</p> <pre> DELETE FROM vegetables WHERE VEGETABLE_IDNOTIN ( SELECT MAX(VEGETABLE_ID) FROM vegetables GROUP BY VEGETABLE_NAME, color ); </pre> <p>Three rows have been deleted.</p> <pre> SELECT *FROM vegetables; </pre> <br> <table class="table"> <tr> <td>VEGETABLE_ID</td> <td>VEGETABLE_NAME</td> <td>COLOR</td> </tr> <tr> <td> <strong>02</strong> </td> <td>Potato</td> <td>Brown</td> </tr> <tr> <td> <strong>05</strong> </td> <td>Onion</td> <td>Red</td> </tr> <tr> <td> <strong>06</strong> </td> <td>Pumpkin</td> <td>Green</td> </tr> <tr> <td> <strong>07</strong> </td> <td><pumpkin td> <td>Yellow</td> </pumpkin></td></tr> </table> <p>If we want to keep the row with the lowest id, use the <strong>MIN()</strong> function instead of the <strong>MAX()</strong> function.</p> <pre> DELETE FROM vegetables WHERE VEGETABLE_IDNOTIN ( SELECT MIN(VEGETABLE_ID) FROM vegetables GROUP BY VEGETABLE_NAME, color ); </pre> <p>The above method works if we have a column that is not part of the group for evaluating duplicate. If all values in the columns have copies, then we cannot use the <strong>VEGETABLE_ID</strong> column.</p> <p>Let&apos;s drop and create the <strong>vegetable</strong> table with a new structure.</p> <pre> DROP TABLE vegetables; CREATE TABLE vegetables ( VEGETABLE_ID NUMBER, VEGETABLE_NAME VARCHAR2(100), Color VARCHAR2(20) ); </pre> <br> <pre> INSERT INTO vegetables (VEGETABLE_ID,VEGETABLE_NAME,color) VALUES(1,&apos;Potato&apos;,&apos;Brown&apos;); INSERT INTO vegetables (VEGETABLE_ID,VEGETABLE_NAME,color) VALUES(1, &apos;Potato&apos;,&apos;Brown&apos;); INSERT INTO vegetables (VEGETABLE_ID,VEGETABLE_NAME,color)VALUES(2,&apos;Onion&apos;,&apos;Red&apos;); INSERT INTO vegetables (VEGETABLE_ID,VEGETABLE_NAME,color)VALUES(2,&apos;Onion&apos;,&apos;Red&apos;); INSERT INTO vegetables (VEGETABLE_ID,VEGETABLE_NAME,color) VALUES(2,&apos;Onion&apos;,&apos;Red&apos;); INSERT INTO vegetables (VEGETABLE_ID,VEGETABLE_NAME,color) VALUES(3,&apos;Pumpkin&apos;,&apos;Green&apos;); INSERT INTO vegetables (VEGETABLE_ID,VEGETABLE_NAME,color) VALUES(&apos;4,Pumpkin&apos;,&apos;Yellow&apos;); SELECT * FROM vegetables; </pre> <br> <table class="table"> <tr> <td>VEGETABLE_ID</td> <td>VEGETABLE_NAME</td> <td>COLOR</td> </tr> <tr> <td>01</td> <td>Potato</td> <td>Brown</td> </tr> <tr> <td>01</td> <td>Potato</td> <td>Brown</td> </tr> <tr> <td>02</td> <td>Onion</td> <td>Red</td> </tr> <tr> <td>02</td> <td>Onion</td> <td>Red</td> </tr> <tr> <td>02</td> <td>Onion</td> <td>Red</td> </tr> <tr> <td>03</td> <td>Pumpkin</td> <td>Green</td> </tr> <tr> <td>04</td> <td>Pumpkin</td> <td>Yellow</td> </tr> </table> <p>In the vegetable table, the values in all columns <strong>VEGETABLE_ID, VEGETABLE_NAME</strong> , and color have been copied.</p> <p>We can use the <strong>rowid</strong> , a locator that specifies where Oracle stores the row. Because the <strong>rowid</strong> is unique so that we can use it to remove the duplicates rows.</p> <pre> DELETE FROM Vegetables WHERE rowed NOT IN ( SELECT MIN(rowid) FROM vegetables GROUP BY VEGETABLE_ID, VEGETABLE_NAME, color ); </pre> <p>The query verifies the deletion operation:</p> <pre> SELECT * FROM vegetables; </pre> <br> <table class="table"> <tr> <td>VEGETABLE_ID</td> <td>VEGETABLE_NAME</td> <td>COLOR</td> </tr> <tr> <td>01</td> <td>Potato</td> <td>Brown</td> </tr> <tr> <td>02</td> <td>Onion</td> <td>Red</td> </tr> <tr> <td>03</td> <td>Pumpkin</td> <td>Green</td> </tr> <tr> <td>04</td> <td>Pumpkin</td> <td>Yellow</td> </tr> </table> <hr></t2.id>

Tre rader hade tagits bort. Vi kör frågan, som ges nedan för att hitta dubbletter av e-postmeddelanden från bordet.

 SELECT email, COUNT (email) FROM contacts GROUP BY email HAVING COUNT (email) > 1; 

Frågan returnerar den tomma uppsättningen. För att verifiera data från kontakttabellen, kör följande SQL-fråga:

instansierar java

 SELECT * FROM contacts; 

Raderna ID 9, 12 och 14 har raderats. Vi använder följande uttalande för att ta bort dubblettraderna:

Kör skriptet för skapande kontakten.

 DELETE c1 FROM contacts c1 INNERJ OIN contacts c2 WHERE c1.id > c2.id AND c1.email = c2.email; 

(B) Ta bort dubbletter av rader med hjälp av en mellantabell

För att ta bort en dubblettrad genom att använda den mellanliggande tabellen, följ stegen nedan:

Steg 1 . Skapa en ny tabell strukturera , samma som den riktiga tabellen:

 CREATE TABLE source_copy LIKE source; 

Steg 2 . Infoga de distinkta raderna från det ursprungliga schemat för databasen:

 INSERT INTO source_copy SELECT * FROM source GROUP BY col; 

Steg 3 . Släpp den ursprungliga tabellen och byt namn på den omedelbara tabellen till den ursprungliga.

 DROP TABLE source; ALTER TABLE source_copy RENAME TO source; 

Till exempel raderar följande uttalanden rader med duplicera e-postmeddelanden från kontakttabellen:

 -- step 1 CREATE TABLE contacts_temp LIKE contacts; -- step 2 INSERT INTO contacts_temp SELECT * FROM contacts GROUP BY email; -- step 3 DROP TABLE contacts; ALTER TABLE contacts_temp RENAME TO contacts; 

(C) Ta bort dubbletter av rader med ROW_NUMBER()-funktionen

Obs: Funktionen ROW_NUMBER() har stöds sedan MySQL version 8.02, så vi bör kontrollera vår MySQL-version innan vi använder funktionen.

Följande uttalande använder ROW_NUMBER () för att tilldela ett sekventiellt heltal till varje rad. Om e-postmeddelandet är duplicerat kommer raden att vara högre än en.

rensa npm-cache

 SELECT id, email, ROW_NUMBER() OVER (PARTITION BY email ORDER BY email ) AS row_num FROM contacts; 

Följande SQL-fråga returnerar id-lista av dubblettraderna:

 SELECT id FROM (SELECT id, ROW_NUMBER() OVER ( PARTITION BY email ORDER BY email) AS row_num FROM contacts ) t WHERE row_num> 1; 

Produktion:

Ta bort dubbletter av poster i Oracle

När vi hittade dubblettposterna i tabellen var vi tvungna att ta bort de oönskade kopiorna för att hålla vår data ren och unik. Om en tabell har dubbletter av rader kan vi ta bort den genom att använda RADERA påstående.

I fallet har vi en kolumn, som inte är en del av grupp brukade utvärdera de duplicera poster i tabellen.

Tänk på tabellen nedan:

 -- create the vegetable table CREATE TABLE vegetables ( VEGETABLE_ID NUMBER generated BY DEFAULT AS ID ENTITY, VEGETABLE_NAME VARCHAR2(100), color VARCHAR2(20), PRIMARY KEY (VEGETABLE_ID) ); 

 -- insert sample rows INSERT INTO vegetables (VEGETABLE_NAME,color) VALUES('Potato','Brown'); INSERT INTO vegetables (VEGETABLE_NAME,color) VALUES('Potato','Brown'); INSERT INTO vegetables (VEGETABLE_NAME,color) VALUES('Onion','Red'); INSERT INTO vegetables (VEGETABLE_NAME,color) VALUES('Onion','Red'); INSERT INTO vegetables (VEGETABLE_NAME,color) VALUES('Onion','Red'); INSERT INTO vegetables (VEGETABLE_NAME,color) VALUES('Pumpkin','Green'); INSERT INTO vegetables (VEGETABLE_NAME,color) VALUES('Pumpkin','Yellow'); 

 -- query data from the vegetable table SELECT * FROM vegetables; 

Anta att vi vill behålla raden med den högsta VEGETABLE_ID och radera alla andra kopior.

 SELECT MAX (VEGETABLE_ID) FROM vegetables GROUP BY VEGETABLE_NAME, color ORDER BY MAX(VEGETABLE_ID); 

Vi använder RADERA för att ta bort raderna vars värden i VEGETABLE_ID COLUMN är inte högsta .

java escape-tecken

 DELETE FROM vegetables WHERE VEGETABLE_IDNOTIN ( SELECT MAX(VEGETABLE_ID) FROM vegetables GROUP BY VEGETABLE_NAME, color ); 

Tre rader har tagits bort.

 SELECT *FROM vegetables; 

Om vi ​​vill behålla raden med lägst id, använd MIN() funktion istället för MAX() fungera.

 DELETE FROM vegetables WHERE VEGETABLE_IDNOTIN ( SELECT MIN(VEGETABLE_ID) FROM vegetables GROUP BY VEGETABLE_NAME, color ); 

Ovanstående metod fungerar om vi har en kolumn som inte ingår i gruppen för att utvärdera duplikat. Om alla värden i kolumnerna har kopior kan vi inte använda VEGETABLE_ID kolumn.

Låt oss släppa och skapa grönsak bord med ny struktur.

 DROP TABLE vegetables; CREATE TABLE vegetables ( VEGETABLE_ID NUMBER, VEGETABLE_NAME VARCHAR2(100), Color VARCHAR2(20) ); 

 INSERT INTO vegetables (VEGETABLE_ID,VEGETABLE_NAME,color) VALUES(1,'Potato','Brown'); INSERT INTO vegetables (VEGETABLE_ID,VEGETABLE_NAME,color) VALUES(1, 'Potato','Brown'); INSERT INTO vegetables (VEGETABLE_ID,VEGETABLE_NAME,color)VALUES(2,'Onion','Red'); INSERT INTO vegetables (VEGETABLE_ID,VEGETABLE_NAME,color)VALUES(2,'Onion','Red'); INSERT INTO vegetables (VEGETABLE_ID,VEGETABLE_NAME,color) VALUES(2,'Onion','Red'); INSERT INTO vegetables (VEGETABLE_ID,VEGETABLE_NAME,color) VALUES(3,'Pumpkin','Green'); INSERT INTO vegetables (VEGETABLE_ID,VEGETABLE_NAME,color) VALUES('4,Pumpkin','Yellow'); SELECT * FROM vegetables; 

I grönsakstabellen, värdena i alla kolumner VEGETABLE_ID, VEGETABLE_NAME , och färg har kopierats.

Vi kan använda rowid , en lokaliserare som anger var Oracle lagrar raden. Eftersom den rowid är unik så att vi kan använda den för att ta bort dubblettraderna.

 DELETE FROM Vegetables WHERE rowed NOT IN ( SELECT MIN(rowid) FROM vegetables GROUP BY VEGETABLE_ID, VEGETABLE_NAME, color ); 

Frågan verifierar borttagningen:

 SELECT * FROM vegetables;