TechCodeview

I denne delen lærer vi forskjellige måter å slette dupliserte rader på MySQL og Oracle . Hvis SQL tabellen inneholder dupliserte rader, så må vi fjerne de dupliserte radene.

Forbereder prøvedata

Skriptet oppretter tabellen med navn kontakter .

 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 ovenfor har vi satt inn følgende data.

finn i streng c++

 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 kjører skriptet for å gjenskape testdata etter å ha utført en SLETT uttalelse .

Spørringen returnerer data fra kontakttabellen:

 SELECT * FROM contacts ORDER BY email; 

Følgende SQL-spørring returnerer de dupliserte e-postene fra kontakttabellen:

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

Vi har tre rader med duplisere e-poster.

(A) Slett dupliserte rader med DELETE JOIN-setningen

 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 var slettet. Vi utfører spørringen, gitt nedenfor for å finne dupliserte e-poster fra bordet.

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

Spørringen returnerer det tomme settet. For å bekrefte dataene fra kontakttabellen, utfør følgende SQL-spørring:

 SELECT * FROM contacts; 

Radene ID-er 9, 12 og 14 har blitt slettet. Vi bruker setningen nedenfor for å slette de dupliserte radene:

Utfør skriptet for skaper kontakten.

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

(B) Slett dupliserte rader ved å bruke en mellomtabell

For å slette en duplikatrad ved å bruke mellomtabellen, følg trinnene nedenfor:

Trinn 1 . Lag en ny tabell struktur , samme som den virkelige tabellen:

linux $home

 CREATE TABLE source_copy LIKE source; 

Steg 2 . Sett inn de distinkte radene fra den opprinnelige planen til databasen:

 INSERT INTO source_copy SELECT * FROM source GROUP BY col; 

Trinn 3 . Slipp den opprinnelige tabellen og gi nytt navn til den umiddelbare tabellen til den opprinnelige.

 DROP TABLE source; ALTER TABLE source_copy RENAME TO source; 

Følgende utsagn sletter for eksempel rader med duplisere e-poster fra 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) Slett dupliserte rader ved å bruke ROW_NUMBER()-funksjonen

Merk: ROW_NUMBER()-funksjonen har blitt støttet siden MySQL versjon 8.02, så vi bør sjekke MySQL-versjonen vår før vi bruker funksjonen.

Følgende uttalelse bruker ROW_NUMBER () for å tilordne et sekvensielt heltall til hver rad. Hvis e-posten er duplikat, vil raden være høyere enn én.

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

Følgende SQL-spørring returnerer id-liste av de dupliserte radene:

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

Produksjon:

Slett dupliserte poster i Oracle

Da vi fant de dupliserte postene i tabellen, måtte vi slette de uønskede kopiene for å holde dataene våre rene og unike. Hvis en tabell har dupliserte rader, kan vi slette den ved å bruke SLETT uttalelse.

I tilfellet har vi en kolonne, som ikke er en del av gruppe pleide å evaluere de duplisere poster i tabellen.

Tenk på tabellen nedenfor:

 -- 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 at vi ønsker å beholde raden med den høyeste VEGETABLE_ID og slett alle andre kopier.

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

Vi bruker SLETT setning for å slette radene hvis verdier i VEGETABLE_ID COLUMN er ikke høyest .

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

Tre rader er slettet.

 SELECT *FROM vegetables; 

Hvis vi vil beholde raden med den laveste id-en, bruker du MIN() funksjon i stedet for MAKS() funksjon.

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

Metoden ovenfor fungerer hvis vi har en kolonne som ikke er en del av gruppen for evaluering av duplikat. Hvis alle verdiene i kolonnene har kopier, kan vi ikke bruke VEGETABLE_ID kolonne.

hvor mange uker i en måned

La oss slippe og lage grønnsak 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ønnsakstabellen, verdiene i alle kolonner VEGETABLE_ID, VEGETABLE_NAME , og farge er kopiert.

Vi kan bruke rowid , en lokator som spesifiserer hvor Oracle lagrer raden. Fordi det rowid er unik, slik at vi kan bruke den til å fjerne duplikatradene.

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

Spørringen bekrefter sletteoperasjonen:

 SELECT * FROM vegetables;