Finding And Replacing Duplicates In Sql Using A Script

Mar 27, 2008

Finding and replacing duplicates

I have this project; I am trying to work on but can€™t because of duplicated data in that table.

I tried to make the culume not accept duplicates, but I need to clean it up first and I can just delete them.

Currently the values are 11 digit numbers and I need to replace them with 00000000001

incrementing, making my last one 00000003000

Any idea on where to even begin? I am new to this.

What I am trying to do.

Find replicates numbers in TRAN_DATA

Replace each with a new number but increment the new number starting with 00000000001 and ending with 00000003000

With this I knew I had 3000 plus.

View 5 Replies


Finding Duplicates In SQL

Jul 26, 2002

I have a customer database with the following structure:

Dept ID (int)
Section (varchar)

I need to find only occurrences of a section (eg Admin) where the section name has a record in Dept 1,2 and 3 - only return the result if the record for Admin is associated will these depts.

Example Data:

Dept Section
1 Admin
1 Finance
1 Marketing
2 Admin
2 Sales
2 Marketing
3 Admin
3 Sales
3 Finance

The query would only return 'Admin' since this is the only Section that is represented in Departments 1,2 + 3.

I am not in control of the data design and can't create a bitwise field to achieve this.


View 1 Replies View Related

Finding Duplicates

Jul 7, 2004

I am trying to complete an insert from query but the problem is I have duplicates, so I'm getting an error message. So to correct it I am creating a Find Duplicates statement in the Query analyzer but Its not working can someone tell me whats wrong with this statement (by the way I'm in SQL 2000 Server)

thank you

SELECT EmployeeGamingLicense [TM#]AS [TM# Field], Count([TM#])AS NumberOfDups
HAVING Count([TM#])>1;

View 14 Replies View Related

Finding Duplicates - Is This Right?

May 16, 2007

I've done a search and I THINK I've got my head round this, but I'd be very grateful if someone could reassure me:

SELECT Email FROM List2 WHERE List2.Email= List1.Email
) AND List1.Email <> '44'

That will give me every email address from list one that (a) appears in list two, and (b) isn't '44'. Right?

And to find all the emails from List1 that DON'T occur in List2 (and aren't '44'), I just put "NOT" in front of "EXISTS". Right?

Sorry for asking an obvious question but I'm having a real mental block here. :o

View 6 Replies View Related

Finding Duplicates

Jun 5, 2008

how can i find duplicates in my database? i want to find them in the website and expressemail field

View 5 Replies View Related

Finding Duplicates Using More That One Key

Jun 16, 2008

Hi guys,

I was just wondering if any one knows how to find duplicate keys using more than one field. I used the below key to find those people who exists in list1 but don't exists in list2. I realized that the results had some duplicates which was expected but how do I then find all those duplicate people. I know how to do it if there was a primary key present I would have done a count (distinct cardnumber) > 1 and i would have done the select statement like this distinct cardnumber, but how do I do it with more that one key??

View 1 Replies View Related

Finding Duplicates

Jul 20, 2005

I have a company table and I would like to write a query that will return tome any duplicate companies. However, it is a little more complicated thenjust matching on exact company names. I would like it to give me duplicateswhere x number of letters at the beginning of the company name match AND xnumber of letters of the address match AND x number of letters of the citymatch. I will be doing this in batches based on the first letter of thecompany name. So for example I will first process all companies that startwith the letter "A".So for all "A" companies I want to find companies where the first 5 lettersin the company name match and the first 5 characters of the address fieldmatch and the first 5 characters of the city match. THANKS!!!

View 3 Replies View Related

Finding Duplicates?

Oct 9, 2007

I'm trying to find duplicates. Any help will be greatly appreciated! Here's my query:

I'm trying to find only the records where the "Sales_Header_Your_Reference" field is used more then once with a different "Sales Header Document ID" So it looks like this
Sales Header Document ID Sales_Header_Your_Reference
1955718 0002377729
2082721 0002478945
2082728 0002478976
2093598 0002487318
2093599 0002487318
2093601 0002487332
I hope im clear enough, here is my query. I get then listed right, but the count is all wrong, I only want to show the ones , like the one i have highlighted.

Code Block

Select COUNT(Sales_Header_Your_Reference),Tbl_Sales_Header.Sales_Header_Document_Id,Tbl_Sales_Header.Sales_Header_Your_Reference
FROM dbo.Tbl_Sales_Header
INNER JOIN dbo.Tbl_Sales_Invoice_Header
ON dbo.Tbl_Sales_Header.Customer_Bill_Customer_No = dbo.Tbl_Sales_Invoice_Header.Sales_Invoice_Header_Bill_Customer_No AND
dbo.Tbl_Sales_Header.Sales_Header_Order_DateTime = dbo.Tbl_Sales_Invoice_Header.Sales_Invoice_Header_Order_Datetime
WHERE (dbo.Tbl_Sales_Header.Sales_Header_Order_DateTime > CONVERT(DATETIME, '2007-09-15 00:00:00', 102)) AND
(dbo.Tbl_Sales_Header.Customer_Bill_Customer_No = 'Butler') OR
(dbo.Tbl_Sales_Header.Customer_Bill_Customer_No = 'MWI') OR
(dbo.Tbl_Sales_Header.Customer_Bill_Customer_No = 'NLS') OR
(dbo.Tbl_Sales_Header.Customer_Bill_Customer_No = 'HSI') OR
(dbo.Tbl_Sales_Header.Customer_Bill_Customer_No = 'NLSMVD') OR
(dbo.Tbl_Sales_Header.Customer_Bill_Customer_No = 'RNEXT')
GROUP by Tbl_Sales_Header.Sales_Header_Document_Id,Tbl_Sales_Header.Sales_Header_Your_Reference
HAVING COUNT(Sales_Header_Your_Reference) > 1
order by Tbl_Sales_Header.Sales_Header_Document_Id, Tbl_Sales_Header.Sales_Header_Your_Reference

View 9 Replies View Related

Finding Duplicates Regardless Of Special Characters - Help Plz

Nov 22, 2005

I am trying to move distinct data from one table to another based on two columns regardless of special characters such as : ( ) ; ' " / - _ = >< etc. The two columns that are the defining factors that I need to match as duplicates are Col1 & Col3, the rest I want to get the maximum data from each column for that row (I hope this made sense).

Here is what I have tried, but it does not seem to work:


max(Col2)as Col2,
max(Col4)as Col4,
max(Col5)as Col5,
max(Col6)as Col6,
max(Col7)as Col7,
max(Col8)as Col8,
max(Col9)as Col9
where patindex('%[^-:]%',Col1) = 0 AND patindex('%[^-:]%',Col3) = 0
into Newtable
from dbo.CLEANING_bk
group by Col1,Col3

Basically if there are 10 rows of duplicate data based on Col1 & Col3 (regardless of special characters), I want to move the distinct info to a new table with the maximum amount of info from the other columns of the duplicate rows.

If row 3, 4,5, & 6 are all considered duplicate and Row3/Col2 has info in it that row 4 & 5/Col2 does not, I want to combine it with the single row I export to the new table retaining as much information from the rows of the duplicates as possible.

Can anyone help and let me know why my script is not wanting to play nice?

View 14 Replies View Related

Finding Duplicates - What On Earth Am I Overlooking?

Aug 15, 2006

I have two lists of contacts. They're similar. I want a list of all the contacts whose email address occurs only in the first list.

returns 13460

returns 13220

returns 0 rows

How can it be returning no rows? What am I failing to take into consideration?


View 6 Replies View Related

Transact SQL :: Comparing Records - Finding Matches / Duplicates

Nov 20, 2015

I have this 40,000,000 rows table... I am trying to clean this 'Contacts' table since I know there are a lot of duplicates.

At first, I wanted to get a count of how many there are.

I need to compare records where these fields are matched:

MATCHED: (email, firstname) but not MATCH: (lastname, phone, mobile).
MATCHED: (email, firstname, mobile)
But not MATCH: (lastname, phone)
MATCHED: (email, firstname, lastname)
But not MATCH: (phone, mobile)

View 9 Replies View Related

SQL Server 2012 :: Finding Duplicates And Show Original / Duplicate Record

Nov 3, 2014

There are many duplicate records on my data table because users constantly register under two accounts. I have a query that identify the records that have a duplicate, but it only shows one of the two records, and I need to show the two records so that I can reconcile the differences.The query is taken from a post on stack overflow. It gives me 196, but I need to see the 392 records.

How to identify the duplicates and show the tow records without having to hard code any values, so I can use the query in a report, and anytime there are new duplicates, the report shows them.



View 5 Replies View Related

T-SQL (SS2K8) :: Rank Duplicates But Only Rows Involved In Duplicates?

Oct 22, 2014

I have a table with 22 million Business records. I can see that there are duplicates when I group by BusinessName and Address and Phone. I'd like to place only the duplicates into a table, with a ranking, oldest business key gets a ranking of 1.

As a bonus I'd like each group to have a distinct group name (although not necessary, just want to know how to do this)

Later after I run more verifications to make sure these are not referenced elsewhere I'll delete everything with a matchRank > 1 out of the main Business table.

DROP TABLE [dbo].[TestBusiness];
CREATE TABLE [dbo].[TestBusiness](
[Business_pk] INT IDENTITY(1,1) NOT NULL,
[BusinessName] VARCHAR (200) NOT NULL,


View 9 Replies View Related

Converting Duplicates Records Into Non Duplicates?

Jan 26, 2015

Is there a query or a way to convert duplicates value in a column to non duplicates.

View 14 Replies View Related

Replacing FIRST In VIEWS

Aug 28, 2000

Hi, I'm trying to conver an Access database to SQL 7, but I can't find a easy way to replicate the aggregate function FIRST in SQL, can anyone give me some advise on how to "fake" the FIRST agg function in SQL7? Thanks

View 1 Replies View Related

Replacing Characters In SQL

Aug 13, 2004

I am incorporating a perl script loading data into my SQL Server. If I receive a message with a single backslash I know to replace it with a double backslash \. But what if it is a " double quote what do I need to do to get it to appear as is?


View 2 Replies View Related

Replacing Value In Column?

Nov 20, 2013

I want to replace the value in a column with the content from listbox.

I have wrote the code like,

Dim i as Integer
dim sql as string
For i = 0 to Customerslist1.ListCount-1
sql = "UPDATE master (gstl) VALUES(" +chrb(34)+Customerslist1.Cell(i,8)+chrb(34)+")"
msgbox"insert ok"

but no error found and the sql command is not executing.

View 5 Replies View Related

Replacing Blanks

Aug 31, 2005

Nevil Mascarenhas writes "Hi SQL team,

I am just a beginer in SQL

Here is sample output of a SQL query

Alarm No Site Name Startdate Starttime

7767 ABC 20-08-05 00:00
7765 XYZ 20-08-05 00:00
7762 ASD
5453 QWE 22-08-05 01:00

In this above example, I would like to replace blank fields in Startdate and Starttime coloumn with XXX

Output required is

Alarm No Site Name Startdate Starttime

7767 ABC 20-08-05 00:00
7765 XYZ 20-08-05 00:00
5453 QWE 22-08-05 01:00

Can you please guide me."

View 1 Replies View Related

Replacing Values

Nov 18, 2005

Hi i have 2 cols

col1 col2
A1 21
A2 22
A3 21
A4 23

I want to create a report such that i should see all values of col1 but in col2 i want to show only value of 21 and the remaining should be zero.
So basically all the other values except 21 should be zero in the report but should not be updated in the database.

So the report should look like

col1 col2
A1 21
A2 0
A3 21
A4 0

can you please tell me how to do that



View 10 Replies View Related

Total Replacing

Jul 20, 2005

Hi,Is it possible to do from one script? We have a set of user's tables like"tbl%". We can get this list very easy using this script:SELECT name FROM sysobjects WHERE xtype = 'U' AND name LIKE 'tbl%' ORDER BYname;We need to change some column names if these names are in a special listthat we have. What can we do? Use FOR EACH ROW? Or what?So, I need to get a column list for each table and check if every columnname is equal to one of the names from the list and then if YES replace itby something or add some symbol to this name. Terrible or possible?Regards,Dmitri

View 4 Replies View Related

Replacing A Character

Jul 30, 2007


I have a table with column type as ntext. I need to modify the column value. I wanted to replace a given characterstring with another one in this column. Any assistance on this is highly appreciated.


View 1 Replies View Related

Replacing Views In SQL CE

Jan 15, 2008

Hi all,

We are trying to migrate a SQL Server 2005 database/schema onto SQL CE. The original SQL Server 2005 schema uses views. And VIEWs are not supported on SQL CE. What can we use as an alternative?

Here are the option I can think of:

Use Tables instead of views : We are currently using this approach. But the disadvantage is that data remains on the DB. We need to clean and reuse the table everytimeCan we use a DataSet or DataTable instead? Any sample code available?
Can we replace them by some in-memory data structure?

View 3 Replies View Related

Replacing All The ' In A Table

Nov 24, 2006

Hi all

I need to remove all the ' in a table and i am having some problems with the script, any help would be great



View 1 Replies View Related

Replacing Text

Jan 21, 2008


I would like to replace all instances of a code that starts with 'GEM' with the word 'Perfect'', and all instances of the code that starts with 'GOLD' replaced with 'Imperfect'.

For example, if I have a table named STONES, and a field within the table STONES.Code contained codes such as 'GEMPART4000', 'GEMPART5000', 'GOLDSIDE2300' and 'GOLDSIDE3000', I want to return the first two codes as the words 'Perfect' for each and the last two codes with the word 'Imperfect' for each.

Similarly, how would it be written so I could select a middle part of the code to use as the trigger to replace the code with something different, e.g. if I wanted to use the 'PART' from the first two codes?

I have discovered that the REPLACE and CASE functions don't work with % (so I am looking for any code that starts with what I stipulate but can end with anything (or using it on either side of the middle part of the code)).

Any help would really be appreciated please.

View 5 Replies View Related

Replacing Column Value

Dec 3, 2007

Hi Guys,

I am faced with a problem that is giving me headaches. I have t1, t2 and t3. I t1 I have students that have a reference to t2 which are the schools they belong to. The problem arises when I see that there is redundancy in t2. There more records for the same schools. This was posible in giving the same school (postcode and name make it the same school) a different ID. In t3 I removed these redundant schools by using fuzzy grouping.

My problem is I want to ensure that the students are put using the schools from t3 instead of t2. So what I need to do is is to replace the redundant id from t2 with the correct id from t3.

Please help. Thans in advance

View 3 Replies View Related

Help Replacing 'where Not Exists' In A Query

Jul 20, 2007

I am trying to clean up an ugly query that's based on trying to find items that exist in one table but not the other.
My tables are like this:
Currently my query looks something like this:
Select Items.* from ITEMS where not exists (select 1 from ORDERITEMS inner join ORDERS on ORDERITEMS.OrderID = ORDERS.OrderID where ORDERITEMS.itemID=ITEMS.itemID and (ORDERS.OrderCompleted=1))
So this query is looking for ITEMS what don't have a corresponding entry in the ORDERITEMS table.  As I understand it this is pretty inefficient as it is going to be executing the sub query in the Not Exists statement for each entry in the ITEMS table.  Is the preferred method to do something along the lines of somehow making the sub query into a derived table and doing a left or right join?
Thanks for reading!

View 7 Replies View Related

Replacing Quotes With Stringbuilder

Aug 2, 2007

Hi, below is the query that i use.update user set user_description=' "+ userDesc +" ' where user_id = 1;userDesc is the value taken from a textbox and is supposed to be used with the update query.userDesc = "kad'nsad'kasnd'nak";The quotes in userDesc is affecting the update. Is there anyway to replace quotes with ' using stringbuilder?

View 1 Replies View Related

Sql Query Replacing Values

Apr 1, 2004

Hi, I have the following query.

sql = "select firstname AS Expr1, lastname AS Expr2, status AS Expr3 from person order by lastname"

Status is either 0,1,2 or 3

How can I use the query to create "a temp Alias" for the query, so that there is a "temp Alias" AS Expr4, AS Expr5, AS Expr6 and AS Expr7.

So if status = 0 then Expr4 = "true" else false
So if status = 1 then Expr5 = "true" else false
So if status = 2 then Expr6 = "true" else false
So if status = 3 then Expr7 = "true" else false

So when the reader reads Expr4 its either true or false.

Is this possible i a query?

View 1 Replies View Related

Replacing A Column With A Foreign Key

Dec 24, 2005

In an *existing* database, how would you remove the City column in the
Employees table, and put in the CityID key column from the Cities table

View 2 Replies View Related

Help Please!!! Replacing Returns And Tabs

Jan 10, 2002

how can I remove newline characters and tabs from a char field in sql.
We are directing the results from a query to an excel and having the newlines in one of the columns is messing up the whole output.

View 1 Replies View Related

Triggers Replacing FK And Constraints

Mar 1, 2001

I am thrawn at the deep end

I have been given the task of moving certain tables (that have PK, FK Constraints) from an existing Database (A) to form another Database (B) but making sure all the references to other tables in the original DB (A) are not affected. I was talled that triggers can solve this but I am not sure where to start. The Question is what I am looking for to replace, and how ??

Please help !!!!!!

View 3 Replies View Related

Help - Replacing Data In Tables

Sep 4, 2005

Need to update data from one table with data from another table.

I'm very, very new to MS SQL Server

I have the following two tables.
First table
Field - empno

Second table
Field - oldempno
Field - newempno

tblemp.empno = tbltemp.oldemno

I need to replace the data in tblEmp.empno with tbltemp.newempno where tblemp.empno = tbltemp.oldempo.

Thank you.

View 1 Replies View Related

Replacing Data From SELECT/WHERE

Jun 26, 2001

Hi, and tanks for taking time to read this question. I am still a novice at SQL (Server 2000)programming.

Is there a way to replace the contents of a column of a table derivied from SELECT and WHERE statements?

For example,

SELECT * FROM table1
WHERE column1 = 'text'

and replace 'text' with say, 'newtext'

Any help would be appreciated!



View 5 Replies View Related

Copyrights 2005-15, All rights reserved