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)
SELECT EmployeeGamingLicense [TM#]AS [TM# Field], Count([TM#])AS NumberOfDups FROM TERMINATION GROUP BY [TM#] HAVING Count([TM#])>1; GO
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??
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!!!
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.
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
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:
select ID, Col1, max(Col2)as Col2, Col3, 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?
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)
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.
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]; GO CREATE TABLE [dbo].[TestBusiness]( [Business_pk] INT IDENTITY(1,1) NOT NULL, [BusinessName] VARCHAR (200) NOT NULL, [Address] VARCHAR(MAX) NOT NULL,
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
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?
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)+")" app.Gudangstock.SQLExecute(sql) app.Gudangstock.commit next msgbox"insert ok" UpdateCustomerList1
but no error found and the sql command is not executing.
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.
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
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?
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)).
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.
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: ITEMSitemID,itemName,itemDescriptionetc... ORDERITEMSOrderLineID,OrderID,itemID ORDERSOrderID,OrderCompleted 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! Ryan
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?
Hi, 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. thanks Zoey
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 ??