7

DELETE DUPLICATE VALUE IN THE SAME COLUMN - SQL

 2 years ago
source link: https://dev.to/hte305/delete-duplicate-value-in-the-same-column-sql-47d
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

DELETE DUPLICATE VALUE IN THE SAME COLUMN - SQL

Aug 31

・1 min read

Sometime, the table has duplicate value as string, number or etc,... And we want to clean that data. But the data is dynamic so we can not hard code for all case of table.

fruits table

id name 1 Orange 🍊 2 Apple 🍎 3 Orange 🍊 4 Pineapple🍍

In this case, we got have a simple sql like that:

DELETE
  FROM fruits a
 USING fruits b
 WHERE a.id > b.id
   AND a.name = b.name;
Enter fullscreen modeExit fullscreen mode

Result after excute query

id name 1 Orange 🍊 2 Apple 🍎 4 Pineapple🍍

If several columns have the same names but the datatypes do not match, the NATURAL JOIN clause can be modified with the USING clause to specify the columns that should be used for an EQUIJOIN.

Find out USING in this article

Enjoy your time 🪴
Thanks for reading.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK