TechCodeview

Ebben a részben megtudjuk, hogyan törölheti az ismétlődő sorokat MySQL és Oracle . Ha a SQL táblázat duplikált sorokat tartalmaz, akkor el kell távolítanunk az ismétlődő sorokat.

Mintaadatok előkészítése

A szkript létrehozza a nevű táblát kapcsolatokat .

 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 ); 

A fenti táblázatba a következő adatokat illesztettük be.

 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'), 

Futtatjuk a szkriptet, hogy újra létrehozzuk a tesztadatokat az a végrehajtása után TÖRÖL nyilatkozat .

A lekérdezés adatokat ad vissza a névjegytáblázatból:

 SELECT * FROM contacts ORDER BY email; 

A következő SQL-lekérdezés a névjegytáblázat ismétlődő e-mailjeit adja vissza:

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

Három sorunk van másolat e-maileket.

(A) Törölje az ismétlődő sorokat a DELETE JOIN utasítással

 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>

Három sort töröltek. Az alábbiakban megadott lekérdezést végrehajtjuk, hogy megtaláljuk a duplikált e-mailek az asztaltól.

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

A lekérdezés az üres halmazt adja vissza. A névjegytáblázat adatainak ellenőrzéséhez hajtsa végre a következő SQL-lekérdezést:

 SELECT * FROM contacts; 

A sorok azonosító: 9, 12 és 14 törölve lettek. Az ismétlődő sorok törléséhez az alábbi utasítást használjuk:

Futtassa a szkriptet a következőhöz létrehozása a kapcsolat.

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

(B) Törölje a duplikált sorokat egy közbenső táblázat segítségével

Egy ismétlődő sor törléséhez a közbenső táblázat használatával, kövesse az alábbi lépéseket:

1. lépés . Hozzon létre egy új táblázatot szerkezet , ugyanaz, mint a valódi táblázat:

 CREATE TABLE source_copy LIKE source; 

2. lépés . Szúrja be az adatbázis eredeti ütemezésétől eltérő sorokat:

 INSERT INTO source_copy SELECT * FROM source GROUP BY col; 

3. lépés . Dobja el az eredeti táblát, és nevezze át a közvetlen táblát az eredetire.

 DROP TABLE source; ALTER TABLE source_copy RENAME TO source; 

Például a következő állítások törlik a sorokat val vel másolat e-mailek a névjegyzékből:

 -- 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) Törölje az ismétlődő sorokat a ROW_NUMBER() függvény segítségével

Megjegyzés: A ROW_NUMBER() függvény a MySQL 8.02-es verziója óta támogatott, ezért a funkció használata előtt ellenőriznünk kell a MySQL verziónkat.

A következő állítás a ROW_NUMBER () hogy minden sorhoz szekvenciális egész számot rendeljünk. Ha az e-mail ismétlődő, a sor magasabb lesz, mint egy.

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

A következő SQL lekérdezés tér vissza azonosító lista az ismétlődő sorok közül:

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

Kimenet:

Törölje a duplikált rekordokat az Oracle-ben

Amikor megtaláltuk az ismétlődő rekordokat a táblázatban, törölnünk kellett a nem kívánt másolatokat, hogy adataink tiszták és egyediek maradjanak. Ha egy táblában ismétlődő sorok vannak, akkor a következővel törölhetjük TÖRÖL nyilatkozat.

Ebben az esetben van egy oszlopunk, amely nem része csoport szokott értékelni a másolat rekordokat a táblázatban.

Vegye figyelembe az alábbi táblázatot:

 -- 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; 

Tegyük fel, hogy a legmagasabb sorral akarjuk tartani VEGETABLE_ID és törölje az összes többi másolatot.

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

Használjuk a TÖRÖL utasítással törölheti azokat a sorokat, amelyek értékei a VEGETABLE_ID OSZLOP nem a legmagasabb .

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

Három sor törölve.

 SELECT *FROM vegetables; 

Ha meg akarjuk tartani a legalacsonyabb azonosítójú sort, használjuk a MIN() funkció helyett a MAX() funkció.

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

A fenti módszer akkor működik, ha van egy oszlopunk, amely nem része a duplikátumok kiértékelési csoportjának. Ha az oszlopok minden értékének másolata van, akkor nem használhatjuk a VEGETABLE_ID oszlop.

Dobjuk fel és hozzuk létre a növényi táblázat új szerkezettel.

parseint java

 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; 

A zöldségtáblázatban az értékek az összes oszlopban VEGETABLE_ID, VEGETABLE_NAME , és színe másolásra került.

Használhatjuk a zsémbes , egy lokátor, amely meghatározza, hogy az Oracle hol tárolja a sort. Mert a zsémbes egyedi, így használhatjuk az ismétlődő sorok eltávolítására.

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

A lekérdezés ellenőrzi a törlési műveletet:

 SELECT * FROM vegetables;