Skip to main content

Delete duplicate record

Q - Delete duplicate data from a table.

A - First of all create a table -

 create table del_duplicate
 (
 id int ,
 name varchar (15),
 class varchar (15)
 )

insert records :

 insert into del_duplicate values ('1','Abhishek','IT')
 insert into del_duplicate values ('1','Abhishek','IT')
 insert into del_duplicate values ('2','Amit','Civil')
 insert into del_duplicate values ('2','Amit','Civil')

 Query for delete duplicate data -:

Method 1:

SELECT DISTINCT id,name,class
INTO    tempTable
FROM    del_duplicate
GO
TRUNCATE TABLE del_duplicate
DROP TABLE del_duplicate
exec sp_rename 'tempTable', 'del_duplicate'

Method 2:

      SET NOCOUNT ON
      SET ROWCOUNT 1
      WHILE 1 = 1
       BEGIN
       DELETE   FROM del_duplicate
      WHERE    id IN (SELECT  id
      FROM    del_duplicate
     GROUP BY id
     HAVING  COUNT(*) > 1)
     IF @@Rowcount = 0
     BREAK ;
     END
     SET ROWCOUNT 0

Comments

Post a Comment

Popular posts from this blog

Select Names from table which have vowels

  Problem Query the list of  CITY  names from  table  which have vowels (i.e.,  a ,  e ,  i ,  o , and  u ) as both their first  and  last characters. Your result cannot contain duplicates. Input Format The  STATION  table is described as follows: Field Type ID NUMBER CITY VARCHAR2(21) STATE VARCHAR2(2) LAT_N NUMBER LONG_W NUMBER STATION where  LAT_N  is the northern latitude and  LONG_W  is the western longitude. MYSQL select distinct city from station where (city like 'a%' or city like 'e%' or city like 'i%' or city like 'o%' or city like 'u%' ) and ( city like '%a' or city like '%e' or city like '%i' or city like '%o' or city like '%u' )
1 comment
Read more
Q- What will be the result of the query below? Explain your answer and provide a version that behaves correctly. select case when null = null then 'Yup' else 'Nope' end as Result; Ans-This query will actually yield “Nope”, seeming to imply that  null  is not equal to itself! The reason for this is that the proper way to compare a value to  null  in SQL is with the  is  operator, not with  = . Accordingly, the correct version of the above query that yields the expected result (i.e., “Yup”) would be as follows: select case when null is null then 'Yup' else 'Nope' end as Result;
Post a Comment
Read more

Methods of Rank Rows in SQL Server: ROW_NUMBER(), RANK(), DENSE_RANK() and NTILE()

SQL Server provides us with a number of window functions that help us to perform calculations across a set of rows, without the need to repeat the calls to the database. Unlike the standard aggregate functions, the window functions will not group the rows into a single output row, they will return a single aggregated value for each row, keeping the separate identities for those rows. The Window term here is not related to the Microsoft Windows operating system, it describes the set of rows that the function will process. One of the most useful types of window functions is Ranking Window Functions that are used to rank specific field values and categorize them according to the rank of each row, resulting in a single aggregated value for each participated row. There are four ranking window functions supported in SQL Server;  ROW_NUMBER(),   RANK() ,  DENSE_RANK()  and  NTILE() . All these functions are used to calculate ROWID for the provided rows window in ...
Post a Comment
Read more