Database-MySQL [3i Infotech Placement]: Sample Questions 37 - 37 of 162

Glide to success with Doorsteptutor material for competitive exams : get questions, notes, tests, video lectures and more- for all subjects of your exam.

Question 37

Edit

Describe in Detail

Essay▾

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
EMPLOYEE_IDATTENDANCE_DATE
012017 - 01 - 01
012017 - 01 - 01
022017 - 01 - 01
022017 - 01 - 01
022017 - 01 - 01
032017 - 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
EMPLOYEE_IDATTENDANCE_DATEAUTOID
012017 - 01 - 011
012017 - 01 - 012
022017 - 01 - 013
022017 - 01 - 014
022017 - 01 - 015
032017 - 01 - 016
  • 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
EMPLOYEE_IDATTENDANCE_DATEAUTOID
012017 - 01 - 012
022017 - 01 - 014
022017 - 01 - 015
  • 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.

Developed by: