Delete duplicates using SQL

-

Use the DISTINCT keyword in conjunction with the DELETE command in SQL to eliminate duplicates in one or more columns of a table.

Here's an example:

Consider a table called test_table with two columns col1 and col2.

Now delete duplicate rows based on both columns:

DELETE FROM test_table WHERE (col1, col2) NOT IN ( SELECT DISTINCT col1, col2 FROM test_table );

In this illustration, the subquery enclosed in brackets chooses all unique sets of values for col1 and col2. Only the rows that are not a part of the distinct set are eliminated thanks to the NOT IN operator in the WHERE clause. Be aware that you must change the query by deleting the second column from the subquery and the comparison in the WHERE clause if you want to eliminate duplicate records based on just one field.

For example, to delete duplicates based on col1 only, you would use:

DELETE FROM test_table WHERE col1 NOT IN ( SELECT DISTINCT col1 FROM test_table );

Always create a backup of the data before executing a delete query since deleting rows from a table might be a dangerous activity.

  1. Deleting duplicates based on a single column:

Consider a table called students with a column name.

Now delete any duplicate rows based on the name column:

DELETE FROM students WHERE id NOT IN ( SELECT MIN(id) FROM students GROUP BY name );

In this example, the subquery selects the minimum id for each distinct name value, and the NOT IN operator in the WHERE clause ensures that only rows that are not part of this set are deleted.

  1. Deleting duplicates based on multiple columns:

Consider a table called orders with two columns customer_id and product_id.

Now delete any duplicate rows based on both columns:

DELETE FROM orders WHERE (customer_id, product_id) NOT IN ( SELECT MIN(customer_id), MIN(product_id) FROM orders GROUP BY customer_id, product_id );

In this example, the subquery selects the minimum customer_id and product_id for each distinct combination of values and the NOT IN operator in the WHERE clause ensures that only rows that are not part of this set are deleted.

  1. Deleting all duplicates:

To delete all duplicates in a table regardless of the columns they are based on, use the ROW_NUMBER() function in combination with a Common Table Expression (CTE):

WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY * ORDER BY id) AS rn FROM test_table ) DELETE FROM cte WHERE rn > 1;

In this illustration, the CTE takes every row from the table and gives each row a distinct row number based on every column (PARTITION BY *). Only the first instance of each row is given a row number of 1, thanks to the ROW_NUMBER() function. The DELETE statement then eliminates any duplicate entries from the table by deleting all rows with a row number greater than 1.

Comments

Post a Comment

Popular posts from this blog

OWASP Top 10

-
OWASP Top 10 is an awareness document mentioning top 10 most critical risks in web application security which is regularly updated by an international non-profit organization called Open Web Application Security Project, dedicated to web application security. 1. Broken Access Control Broken access control vulnerability allows attackers to read / view sensitive data and carry out actions that they are not authorized to carry out. As an illustration, consider a website allows only admin users to view its admin pages and protected from regular users. If access control is compromised, an unauthorized user can read private data belonging to other users, gain access to admin and carry out actions that they are not intended to. Example of such type of vulnerability is Insecure Direct Object References (IDOR). Consider a user on a website uses following URL to access his / her account: https://xyz.com/user_acct?user_num=123 Using the URL, user retrieving his information from the database in th
Read more

TCP/IP Model

-
Image
TCP/IP stands for Transmission Control Protocol/Internet Protocol and is a standard protocol suite used to facilitate communication between network devices over the internet which consists of two components TCP and IP. TCP/IP is a compact form of OSI model. Unlike the OSI model, which has seven layers, it has four layers. The data is divided into packets by the TCP/IP Model at the sender's end, and in order to preserve data correctness, the same packets must be reassembled at the recipient's end. The data is divided into four layers by the TCP/IP paradigm, which arranges the data in a sequential fashion at the sender's end and reorders it at the recipient's end. Network Access Layer: A combination of Physical layer & Data Link layer defined in OSI model, responsible for the transmission of the data between two devices over network. Ethernet protocol is used by this layer. Network Layer / Internet Layer: It is responsible to send the packets from a network to the des
Read more

AAA

-
Image
AAA stands for Authentication, Authorization & Accounting. AAA is an information security framework used for controlling access to computer resources, enforcing policies & tracking for auditing usage of resources required for billing of resources.                           AAA used to control which user is allowed to access the network resources by comparing user's login credentials (user id & password) with AAA server's database (Active Directory) stored user credential data called Authentication, authorize that user for performing a pre-defined task as per roles & responsibilities set in the database (enforcing policies) called Authorization and tracking the actions performed while accessing the network called Accounting. Here AAA server acts as an Identity & Access Management Tool. For example, administrator can access a network device by using console normally.  In case of accessing network device remotely, it can happen only by using IP address. But the
Read more