3i Infotech Papers: Sample Questions 284 - 284 of 1245

Examrace Placement Series prepares you for the toughest placement exams to top companies.

Question number: 284

» Database » MySQL

Essay Question▾

Describe in Detail

How to remove duplicate records from a table?

Explanation

  • Firstly, we will create a table, where we will insert some duplicate rows to understand the topic properly.

  • Create a table called ATTENDANCE by using the following code:

    CREATE TABLE [dbo]. [ATTENDANCE]

    (

    [EMPLOYEE_ID] [varchar] (50) NOT NULL,

    [ATTENDANCE_DATE] [date] NOT NULL

    )

    ON [PRIMARY]

    Now insert some data into a table.

    INSERT INTO dbo. ATTENDANCE (EMPLOYEE_ID, ATTENDANCE_DATE) VALUES

    (‘01’, CONVERT (DATETIME, ‘01 - 01 - 17’, 5) )

    INSERT INTO dbo. ATTENDANCE (EMPLOYEE_ID, ATTENDANCE_DATE) VALUES

    (‘01’, CONVERT (DATETIME, ‘01 - 01 - 17’, 5) )

    INSERT INTO dbo. ATTENDANCE (EMPLOYEE_ID, ATTENDANCE_DATE) VALUES

    (‘02’, CONVERT (DATETIME, ‘01 - 01 - 17’, 5) )

    INSERT INTO dbo. ATTENDANCE (EMPLOYEE_ID, ATTENDANCE_DATE) VALUES

    (‘02’, CONVERT (DATETIME, ‘01 - 01 - 17’, 5) )

    INSERT INTO dbo. ATTENDANCE (EMPLOYEE_ID, ATTENDANCE_DATE) VALUES

    (‘03’, CONVERT (DATETIME, ‘01 - 01 - 17’, 5) )

    INSERT INTO dbo. ATTENDANCE (EMPLOYEE_ID, ATTENDANCE_DATE) VALUES

    (‘03’, CONVERT (DATETIME, ‘01 - 01 - 17’, 5) )

After inserting the data, check the data of the below table. If we grouped the employee_id and attendance_date, then 01 and 02 become duplicates.

Table shows the employee data

Table shows the employee data

EMPLOYEE_ID

ATTENDANCE_DATE

01

2017 - 01 - 01

01

2017 - 01 - 01

02

2017 - 01 - 01

02

2017 - 01 - 01

02

2017 - 01 - 01

03

2017 - 01 - 01

Solution of how to delete those duplicate data?

  • First, insert an identity column in that table by using the following code:

    ALTER TABLE dbo. ATTENDANCE ADD AUTOID INT IDENTITY (1,1)

Now, the table data will be like the following table:

Table shows the employee data

Table shows the employee data

EMPLOYEE_ID

ATTENDANCE_DATE

AUTOID

01

2017 - 01 - 01

1

01

2017 - 01 - 01

2

02

2017 - 01 - 01

3

02

2017 - 01 - 01

4

02

2017 - 01 - 01

5

03

2017 - 01 - 01

6

  • Now use the following code to find out the duplicate rows that exist in the table.

    SELECT * FROM dbo. ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN (AUTOID) _

    FROM dbo. ATTENDANCE GROUP BY EMPLOYEE_ID, ATTENDANCE_DATE)

The above code will give us the following result

Table shows the employee data

Table shows the employee data

EMPLOYEE_ID

ATTENDANCE_DATE

AUTOID

01

2017 - 01 - 01

2

02

2017 - 01 - 01

4

02

2017 - 01 - 01

5

  • There are the duplicate rows, which we want to delete to resolve the issue.

  • Use this code below.

    DELETE FROM dbo. ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN (AUTOID) _

    FROM dbo. ATTENDANCE GROUP BY EMPLOYEE_ID, ATTENDANCE_DATE)

  • Therefore, in the table no duplicate rows exits.