Deleting Duplicates Which Occur In Only One Column
Aug 9, 2006Hello all. I have a table with two coulmns CODE and DESCRITPION. Can anyone suggest how i can go about deleteing the entire record where two or more codes are the same?
Thanks.
ADVERTISEMENT
Deleting Duplicates
Apr 17, 2001Hi,
does anyone out there know how to delete dulicate records in a table? I've looked in BOL and I can't seem to find it. Please advise.
thanks, mark
Deleting Duplicates
Sep 21, 2004Hey all.
I have a table with 100,000 plus records in it, and some are duplicates. Is there any way to delete one of them and not the other. For instance, if I duplicate the table I could run this query.
<cfquery name="query1" datasource="datasource">
DELETE DISTINCT
FROM tablename
WHERE FirstName in ( SELECT FirstName from tablename1 where tablename1.FirstName = tablename.FIRST_NAME AND tablename1.LastName = tablename.LAST_NAME AND tablename1.State = tablename.STATE)
</cfquery>
However, it doesn't work. I know the distinct is not correct. But does anyone know how to achieve this, I have looked all over, and everything I try deletes both records. I was thinking of using some kindof count statement, but it still deletes both of them. Please help. Thanks
Deleting More Duplicates
Jul 25, 2006Hello,I have a stored procedure that deletes duplicatesin one table:.....ASBEGINDELETE FROM mytableWHERE Id IN(SELECT Max(id)from mytablegroup by date, idsenshaving count(*) >1)ENDsometimes it happens that I have >2 rows with duplicated values.How can I write a new stored procedure that delete all rows withduplicated infomrations (leaving only one row with those values)?ThanksM.A.
View 3 Replies View RelatedDeleting Duplicates From Around 25 Tables
Jan 6, 2014I need to remove duplicate data from around 25 tables. I want to use a while loop to go through all tables. If I list out all of the column names the query runs fine, but since there are 25 tables some with 50 plus columns I was hoping to use something like the following, which errors out because my sub queries return more than one result.
SELECT q.* from
(Select ROW_NUMBER() OVER ( Partition BY (SELECT [name] AS [Name] FROM syscolumns
WHERE id = (SELECT id FROM sysobjects
WHERE type = 'U'
AND [Name] = 'Orders')
)
Order by (select top 1 [name] AS [Name] FROM syscolumns
[Code] ....
Deleting Semi Duplicates
Jul 20, 2005Suppose that I have a table that contains a lot of records that areidentical except for an id field and a date-time-stamp field. ForexampleId Unit Price DTS1 A 1.00 Date 12 A 1.00 Date 23 A 1.00 Date 34 B 1.25 Date 45 B 1.50 Date 56 B 1.50 Date 67 C 2.75 Date 78 C 2.75 Date 89 C 2.75 Date 910 C 3.00 Date 10I want to cull out records that are duplicates in the units and pricefields. I want to use the max DTS as the criteria for which record ina set of "duplicates" will remain. So, If I get the right query, Ishould return withId Unit Price DTS1 A 1.00 Date 14 B 1.25 Date 45 B 1.50 Date 57 C 2.75 Date 710 C 3.00 Date 10Is this possible using a single query? If so, how? I am sure that Ican do this using code, but it will involve a bunch of loops andprocess time. I would prefer a cleaner, more elegant way. Thanks forany help.Jerry
View 4 Replies View RelatedDetecting && Deleting Duplicates In Batch Vs Proc
Aug 22, 2006I know how to detect & delete dups/or >dups in test with a select clause, this works fine in a small table, but if the table has a million rows say, it sounds like a proc would be faster: my question is: How do I display those rows in a proc for detecting what the problem is. The print stmt. doesn't seem to work and I wondered if I had to go through the process of building an output stream. The proc creates okay but I'm stuck after that part.
thx
Kat -- very rough code below
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc dupcount
@count int
as
set nocount on
select categoryID,
CategoryName,
Count(*) As Dups
from Categories
group by Categoryid, CategoryName
having count(*) >1
set @count = @@Rowcount
print convert(varchar(30),CategoryID) + ' ' + convert(varchar(30),@count) + CategoryName
/*set @count = @@RowCount
IF @count > 1
print convert CategoryID, CategoryName, convert(varchar(30),@count)*/
Transact SQL :: Differentiate Duplicates In A Table Without Deleting?
Jul 27, 2015How would you differentiate duplicates in a table without deleting.
I have a table name ANGEL that contains duplicate value and i want to append a letter V to the duplicates instead of deleting them.
SQL Server 2014 :: Update Duplicates In A Column
Aug 13, 2015How do I append an accumulating number to duplicates in a column? I would prefer to update the table
for example
Name
John
John
Tom
Bob
Bob
Bob
Mike
to look like
Name
John
John1
Tom
Bob
Bob1
Bob2
Mike
How To Bring Back A List Of Duplicates From A Column
Jul 20, 2005I have a column that has 75 values, 50 are unique/25 are duplicates. Ineed to be able to bring back a list of the duplicates, listing allrows even if more than two have the same value. I need to be able todo this using t-sql.Thanks,Tim
View 5 Replies View RelatedRemoving Duplicates Records From A Column Populate Randomly?
Jan 26, 2015I ran this query to populate a field with random numbers but it keeps populating with some duplicate records. how I can remove the duplicates?
UPDATE APRFIL
SET ALTATH = CONVERT(int, RAND(CHECKSUM(NEWID())) * 10000);
Below are sample output that I need the dupes not show. The table already exist and its sql 2008
155957
155957
155968
155974
155976
15599
155990
155997
155997
156005
156008
Duplicates Of Part Num To Be Summed And Add Another Column Called User
Aug 25, 2015There are 3 columns in the result set - part num, Qty and MO num. Each MO num has part numbers.So there might be same part numbers in MO's. Each part num has qty. So, if I group by part num, I get Qty.
1.There are duplicates of part.num and I want to remove duplicates and add quantities of those duplicates into one single quantity. For example, xxxx is a part num, then xxxx=1,xxxx=3,xxxx=5. I want xxxx=9. I want to sum those. Another question is, each MO has a user. I want to join user and MO num in MO.
Heres the code,
part.num , (woitem.qtytarget/wo.qtytarget) AS woitemqty,
(SELECT LIST(wo.num, ',') FROM wo INNER JOIN moitem ON wo.moitemid = moitem.id WHERE moitem.moid = mo.id) AS wonums FROM mo INNER JOIN moitem ON mo.id = moitem.moid
LEFT JOIN wo ON moitem.id = wo.moitemid
LEFT JOIN woitem ON wo.id = woitem.woid AND woitem.typeid = 10 LEFT JOIN (Select sum(woitem.qtytarget) as labor, woitem.woid, uom.code as uom from woitem JOIN part on woitem.partid = part.id and part.typeid = 21 JOIN uom on woitem.uomid = uom.id group by 2,3) as labor on wo.id = labor.woid LEFT JOIN part ON woitem.partid = part.id
SQL Server 2008 :: How To Find Duplicates In A Table As Per Particular Column
Nov 2, 2015I am having a main table and temptable.
Every sunday, new data will be loaded from temptable to main table. I have to make sure that, duplicates does not get loaded from temptable to maintable.
For example, if last sunday a record gets loaded from temp to main. If this sunday also the same record is present then it means that is a duplicate.
The duplicate is decided on below scenario
select 'CodeChanges: ', count(*) from CodeChanges a, CodeChanges_Temp b
where a.AccountNumber = b.AccountNumber and
a.HexaNumber = b.HexaNumber and
a.HexaEffDate = b.HexaEffDate and
a.HexaId = b.HexaId and
[Code] ...
Yesterday (Sunday) , data from temp got loaded onto maintable but with duplicates.
There is a log which just displays number of duplicates.
Yesterday the log displayed 8 duplicates found. I need to find out the 8 duplicates which got loaded yesterday and delete it off from main table.
There is a column in both tables which is 'creation date and time'. Every Sunday when the load happens this column will have that day's date .
Now i need to find out what are all the duplicates which got loaded on this sunday.
The total rows in temp table is : 363
No of duplicates present is : 8
I used below query to find out the duplicates but it is returning all the 363 rows from the maintable instead of the 8 duplicates.
Select 'CodeChanges: ', * from CodeChanges a where
exists
( Select 1 from CodeChanges_Temp b where
a.HexaNumber = b.HexaNumber and
a.HexaEffDate = b.HexaEffDate and
[Code] ...
Need finding the duplicate records which has creation date time as '2015-11-01 00:00:00.000' and all the above columns mentioned in the query matches.
Example
Few colums only metioned below
creationdateandtime HexaNumber HexaCode
1. 1987-10-01 00:00:00.000 5 3
2. 2015-11-01 00:00:00.000 5 3
So here the second record is duplicate. This is what I am trying to find.
Need To Find Instances Of Duplicates Within A Column; Joining 2 Tables.
Aug 22, 2007My basic situation is this - I ONLY want duplicates, so the oppositeof DISTINCT:I have two tables. Ordinarily, Table1ColumnA corresponds in a one toone ratio with Table2ColumnB through a shared variable. So if I queryTableB using the shared variable, there really should only be onrecord returned. In essence, if I run this and return TWO rows, it isvery bad:select * from TableB where SharedVariable = 1234I know how to join the tables on a single record to see if this is thecase with one record, but I need to find out how many, among possiblymillions of records this affects.Every record in Table1ColumnA (and also the shared variable) will beunique. There is another column in Table1 (I'll call itTable1ColumnC) that will be duplicated if the record in Table2 is aduplicate, so I am trying to use that to filter my results in Table1.I am looking to see how many from Table1 map to DUPLICATE instances inTable2.I need to be able to say, in effect, "how many unique records inTable1ColumnA that have a duplicate in Table1ColumnC also have aduplicate in Table2ColumnB?"Thanks if anyone can help!-- aknoch
View 1 Replies View RelatedDeleting A Column DEFAULT....
Jul 24, 2006Hi,I've the following problem. I must delete a column DEFAULT from a table,but I must do it with a script, independently from the server where it'llbe executed.Locally I've tried with:ALTER TABLE [dbo].[PlanningDettaglio]DROP CONSTRAINT [DF__PlanningD__OreSt__688C6DAC]GOALTER TABLE [dbo].[PlanningDettaglio]ALTER COLUMN [OreStraordinario] varchar(5)GOALTER TABLE [dbo].[PlanningDettaglio]ADD DEFAULT ('00.00') FOR [OreStraordinario]GOit works, but only locally.I've tried with:ALTER TABLE PlanningDettaglio ALTER COLUMN OreStraordinario DROP DEFAULTErr.: Incorrect syntax near the keyword 'DEFAULT'.Can someone help me please?Thanks in advance,GiacomoP.S.We're using SQL Sever 2000 (version 8.00.194)
View 6 Replies View RelatedDuplicates Again! UNION Join - Remove Records With Column Diff.
Sep 9, 2004Hello All,
We all were new at one point.... any help is appreciated.
Objective:
Combining two 49,000 row tables and remove records where there is only 1 column difference. (keeping the specified column value removing the one with a blank.)
Reason:
I have 2 people going through a list, coding a specific column with a single letter value. They both have different progress on each sheet. Hence I am trying to UNION them and have a result of their combined efforts without duplicates.
My progress/where I'm stuck:
Here is my first query/union:
SELECT * FROM [Eds table]
UNION SELECT * FROM [Vickis table];
As shown above, I have unioned these 2 tables and my results removed th obvious whole record duplicates, but since 1 column is different on these, a union without criteria considers them unique.....
an example of duplicates that I must remove are as follows:
142301 - Product 5000 - 150# - S (Keep)
142031 - Product 5000 - 150# - "" <--- Blank (Remove)
I am trying to run another query on my first query results so I don't mess my first query up. Here it is:
SELECT DISTINCT [Prod #], [Prod Name], [Prod Description], [Product Type]
FROM [Combined Tables]
WHERE [Product Type]<>" ";
Please Help! Thank you in advance.
--------------------
5 minutes away from pulling my last one!
BaldNAskewed
SQL 2012 :: How To Check Existing Values In A Column For Duplicates Before Inserting
Aug 12, 2014I have the following objective:
1. I want to check a column to see if there are values (to Eliminate dups)
2. Once checked the values in a column, if not found insert the new value
Here is my code for this:
ALTER TRIGGER DUPLICATES ON AMGR_User_Fields_Tbl
-- When inserting or updating
AFTER INSERT, UPDATE AS
-- Declare the variables
DECLARE @AN varchar(200)
[Code] ....
Reporting Services :: Remove Duplicates Based On A Specific Column
Jun 22, 2015I have some duplicate values for my query results, about 200 duplicates out of 30000 rows. Of these 200 duplicates I want to keep the ones that have a higher value for... 'UpdatedBatchID'.
SELECT
IR.Id as 'ID'
, CAST(IR.Priority as varchar) as 'Priority'
, IRSupportGroupDN.DisplayName as 'Support Group'
, DATEADD(MI,DATEDIFF(mi,GETUTCDATE(),GETDATE()),IR.CreatedDate) as 'Created Date'
, DATEADD(MI,DATEDIFF(mi,GETUTCDATE(),GETDATE()),IR.ResolvedDate) as 'Resolved Date'
, SLOConfig.DisplayName as 'SLO'
, DATEADD(MI,DATEDIFF(mi,GETUTCDATE(),GETDATE()),SLOFact.TargetEndDate) as 'SLO Target'
, SLOStatusDN.DisplayName as 'SLO Status'
, SLOMetric.DisplayName as 'SLO Metric'
, SLOFact.UpdatedBatchId as 'UpdatedBatchID'
Deleting Emoving Column From A Table.
Oct 18, 2000Hello people
Can somebody tell me if and then how to remove a column from a table. (I know how to add a column.)
Kind regards
De Waal
Searching A Column Value Of One Table By Another Then Deleting Row
Nov 9, 2007Hello... I'm real new with SQL Server Express and I need to do the following.
I have two tables...
Table 1: Column EmailAddress
Table 2: Column Cust_ID, EmailAddress, FirstName, LastName
I want to search Table 2 EmailAddress, with Table 1 EmailAddress, and if any of the Email Addresses in Table 1 are found in Table 2, delete the rows in Table 2.
Thanks,
Car54
List When Changes Occur
Jul 20, 2005Hi I am a newbie to SQL.I have a historical list of digatal points listed by time.ie: 3 fieldsPointName;Date/Time;State.I need to return a list of When a specific point chsnges state.For example a list everytime Point A transitions to State 1.Any help is appreciated.--Posted via http://dbforums.com
View 1 Replies View RelatedT-SQL (SS2K8) :: Rank Duplicates But Only Rows Involved In Duplicates?
Oct 22, 2014I 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];
GO
CREATE TABLE [dbo].[TestBusiness](
[Business_pk] INT IDENTITY(1,1) NOT NULL,
[BusinessName] VARCHAR (200) NOT NULL,
[Address] VARCHAR(MAX) NOT NULL,
[code]....
SQL Server 2014 :: Duplicates Of Part-number To Be Summed And Add Another Column Called User?
Aug 25, 2015There are 3 columns in the result set - part num, Qty and MO num. Each MO num has part numbers.So there might be same part numbers in MO's. Each part num has qty. So, if I group by part num, I get Qty.
1.There are duplicates of part.num and I want to remove duplicates and add quantities of those duplicates into one single quantity. For example, xxxx is a part num, then xxxx=1,xxxx=3,xxxx=5. I want xxxx=9. I want to sum those. Another question is, each MO has a user. I want to join user and MO num in MO.
Heres the code,
part.num , (woitem.qtytarget/wo.qtytarget) AS woitemqty,
(SELECT LIST(wo.num, ',') FROM wo INNER JOIN moitem ON wo.moitemid = moitem.id WHERE moitem.moid = mo.id) AS wonums FROM mo INNER JOIN moitem ON mo.id = moitem.moid
LEFT JOIN wo ON moitem.id = wo.moitemid
LEFT JOIN woitem ON wo.id = woitem.woid AND woitem.typeid = 10 LEFT JOIN (Select sum(woitem.qtytarget) as labor, woitem.woid, uom.code as uom from woitem JOIN part on woitem.partid = part.id and part.typeid = 21 JOIN uom on woitem.uomid = uom.id group by 2,3) as labor on wo.id = labor.woid LEFT JOIN part ON woitem.partid = part.id
Handling Failover Occur Using T-SQL
May 10, 2007Hi all,
With C# or VC++ we can use ADO.NET that support the system work smothly when failover occur. I would like to handle failover in t-sql enviroment and it seam to be hard for me when swiching ":connect <servername> code
Do you have any idea to handle it with T-SQL. I need to make a demo on it. Please help!
Integration Services :: Deleting Column From CSV File Using SSIS
Aug 18, 2015How can we delete the column from SSIS file using ssis?
View 3 Replies View RelatedConflicts Consistently Occur On Merge
Aug 15, 2007Hi,
I was hoping a sharp eye may be able to pick up on what I am doing wrong here, I am i must be making a comon mistake. If necessary I am happy to create and post a sample if necessary.
Problem:
The publication parent table has a filter (defined below) and also has a join filter(defined below) to a child table. When changes are made to the parent and child table and synchronised a conflict is raised. For the child table for some reason the merge agent is saying an explicit update occured at the publisher but the values at the publisher have not changed at all and no sql has been executed to update the publisher rows. This in effect is stopping the changes to the child table being applied until i manually apply them in the conflict resolver. The strange thing is the conflict should not occur in the first place.
Perhaps my SQL server 2005 version? 9.00.2050.00 SP1 Standard Edition
Environment:
SQL Server 2005 - 9.00.2050.00 SP1 Standard Edition
SQL Server Mobile
Detail:
FKs are
FK: Job.JobScheduleID --> JobSchedule.ID
FK: JobDetail.JobID --> Job.ID
All three tables have int based IDs that are auto increment.
Publication Articles:
JobSchedule is download only
Job is Bidirectional, identity range management is MANUAL (only updates occur on this table)
JobDetail is Bidirectional, identity range management is MANUAL (only updates occur on this table)
Filters are of the following form:
Filter Job:
SELECT <published_columns> FROM [dbo].[Job] WHERE convert(nchar,[companyID])=Host_Name() AND [JobCompletedDate] IS NULL AND
( [JobScheduleID] in (SELECT distinct ID from JobSchedule where GETDATE() BETWEEN [JobSchedule].[start] AND [JobSchedule].[end]) )
Join Filter Job --> JobDetail
SELECT <published_columns> FROM [dbo].[Job] INNER JOIN [dbo].[JobDetail] ON [Job].[ID] = [JobDetail].[JobID]
Now the first thing to note with respect to using GetDate() i have read http://msdn2.microsoft.com/en-us/library/ms365153.aspx so i thought that i would remove that portion of the Filter on the Job table just to see what happens.
SELECT <published_columns> FROM [dbo].[Job] WHERE convert(nchar,[companyID])=Host_Name() AND [JobCompletedDate] IS NULL
This still did not resolve the issue. I then Remove the AND [JobCompletedDate] IS NULL and it then started working fine (cool) but of course no longer satisfys the rule i want to create (seriously uncool ).
Any ideas out there ?
Much appreciated,
pdns
Possible To Avoid The FILLING Of GAP (created By Deleting A Record) In The Identity Column ?
Jun 18, 2007Hi all,
(I am using SQL Server 2005)
I have created a new 'CUSTOMERS' table and created a colum 'CustomerID' as an Identity column.
Now, a problem I find is that when I delete a particular record, its Identity value is used automatically for the New record I insert later!
I do not want to re-use the already used Identity value.
I just want to have the last CustomerID to be higher that all the previous ones.
Is there any way to do this?
Thanking you in advance,
Tomy
Bcp Error: Truncation Cannot Occur For BCP Output File
Jul 17, 2007Hi,
I'm trying to export sql table as fixed length text file with format file but I got the following error message:
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]
Warning: Server data (61 bytes) exceeds host-file field length (60 bytes) for field (4).
Use prefix length, termination string, or a larger host-file field size.
Truncation cannot occur for BCP output file
All fields in the SQL input table have char data type and I use the format file like this:
7.0
29
1 SQLCHAR 0 10 "" 1 SEQ
2 SQLCHAR 0 1 "" 2 NPARSED
3 SQLCHAR 0 115 "" 3 COMPANY
4 SQLCHAR 0 60 "" 4 ADDR1
....
27 SQLCHAR 0 1 "" 27 LACS
28 SQLCHAR 0 2 "" 28 DPV
29 SQLCHAR 0 2 "
" 29 ZIP4CODE
I've been researched about this error but I couldn't find the clear answer.
The strange thing is that all the records are char() fields, not varchar()
And I checked the max length record for the 4th column(ADDR1) and it was 60, not 61.
However I'm still getting the error.
The output file was exported but some of the records have short length.
Is this some kind of bcp bug?
I used SQL Server 2000 Standard w/ SP4
And the following is the command that I used:
declare @cmd varchar(2000)
SET @cmd = 'bcp "Input_table" out "D:AddressUpdateTmpxFixADDR.dat" -fD:AddressUpdateTmpxFixADDR.fmt -Usa -Psapass -SMyMachine''
print(@cmd)
EXEC master..xp_cmdshell @cmd
Please let me know if anyone solve the similar problem.
Thanks,
- Hyung -
DeadLock Occur While Execute SqlDataAdapter.Fill Method
Mar 25, 2008Hi,
I am using following code to fill my dataset. when multiple user connect to the web-site it will give me following error:
Error Message: Transaction (Process ID 98) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
I am using stored procedure to select records from database.
DataSet ds = new DataSet();SqlConnection mc = OpenSqlConnection();
try
{SqlCommand cmd = new SqlCommand(strCmd, mc);cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;if (parameters != null)
{foreach (KeyValuePair<string, object> p in parameters)
{
cmd.Parameters.AddWithValue(p.Key, p.Value);
}
}SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
}catch (Exception ex)
{
}
finally
{
mc.Close();
}
return ds;
Appreciate your help,
prashant
Error Occur While Delete Large Volum Records
Jan 6, 2004I had write a ActiveX service to delete several tables and those records are more than 100000. When I test it by deleted 1000 records it is ok, but once the volum is increase until 100000, it will give me a error message said timeout operation fail.
how can i overcome this problem. please!!!!
Transport-level Errors Occur After Adding New IP Address
Apr 30, 2008We get this error when we add IP addresses to the Windows system when SQL 2005
database activity is on-going:
Database error: A transport-level error has occurred when receiving results
from the server. (provider: TCP Provider, error: 0 - The semaphore timeout
period has expired.)
.NET application can be running for weeks without error, but after adding a
new IP address, application gets 5-16 'transport-level errors' before
correcting itself.
Error occurs on Windows XP computer in our case. SQL Server, running on Windows server 2003, doesn't seem to pick up on the newly added IP address.
Deleting The Master Table Withour Deleting The Child Tables
Aug 9, 2007Hi
i have to delete the master table data without deleting the child table records,is there any solution for this, parent table has relation with the child table.
regards
vinod.t.v
Converting Duplicates Records Into Non Duplicates?
Jan 26, 2015Is there a query or a way to convert duplicates value in a column to non duplicates.
View 14 Replies View Related