Query To Update 1 Record In A Duplicate Set Of Records
Jul 3, 2007
How do I update a record that has duplicates. For example, I have 3612 orders some of these orders have multiple orderid's I want to update the record for each of these orders that was added most recently.
I have a table that "Geography" that has the following columns: city, state, zip
There are tons of duplicate cities in this table. I ran this query and it shows me the number of occurrences of each city. I want to delete all the duplicates except for 1. I don't want to do this manually as there are a lot of records.
What would the SQL look like to delete the duplicate records but keep at least one?
Saju Kerala Balaji Bangalore Raj Kumar Tamilnadu Saju Kerala
I want to Update one of the duplicate row as I don't have any unique id column. So can anybody update one of the the duplicate record without using any id or altering any column.
I am new to SQL and these forums, so please bear with me :)
My basic question is if I can create a update trigger that will pull info from another record in the same table if data in certain fields match the existing record.
An example:
The user creates a new record. If said user enters data in specified fields that matches data in the same fields in another record in the same table, can a update trigger be used to fill out the rest of this record with the data from the record that matches?
If you need more Info on my problem, ask and I will try to explain better. There may be a better way of doing this than using a trigger, but I am not sure. The fields that I would use to match the data would not be the primary key fields.
I'm getting duplicate records for the last record in the datatable. No matter how much or how little my datatable contains row records, it always duplicate the last one for some reason. Is there something wrong with my code below? EXAMID pulling from another stored procedure, which is outputed back to a variable. ---Data Access Layer---- If dt.Rows.Count > 0 Then 'INSERT EXAM ROSTERInsertComm = New SqlCommandsqladapter = New SqlDataAdapterInsertComm = New SqlClient.SqlCommand("ExamOfficers_AddOfficerSpecificExamRoster", conndb)InsertComm.CommandType = CommandType.StoredProcedure sqladapter.InsertCommand = InsertCommInsertComm.Parameters.Add("@examid", SqlDbType.Int)InsertComm.Parameters("@examid").Value = examidInsertComm.Parameters.Add("@officerid", SqlDbType.Int, 12, "Officer_UID")InsertComm.Parameters.Add("@reimburse", SqlDbType.Bit, 12, "ReimburseToDb")InsertComm.Parameters.Add("@posttest", SqlDbType.Int, 12, "Post_Test")InsertComm.Parameters.Add("@pqcdate", SqlDbType.DateTime, 12, "pqc_date")InsertComm.Parameters.Add("@pqcscore", SqlDbType.Int, 12, "pqc_score") conndb.Open() sqladapter.UpdateBatchSize = 100InsertComm.UpdatedRowSource = UpdateRowSource.Nonesqladapter.Update(dt) InsertComm.ExecuteNonQuery()InsertComm.Dispose() End If ----Stored Procedure---- ALTER PROCEDURE [dbo].[ExamOfficers_AddOfficerSpecificExamRoster] @ExamID as int,@OfficerID as int,@reimburse as bit=NULL,@posttest as int=NULL,@pqcdate as datetime=NULL,@pqcscore as int=NULL ASBEGIN SET NOCOUNT ON; Insert Into Exam_Officers(EXAM_UID,Officer_UID,reimburse,post_test,pqc_date,pqc_score)values(@ExamID,@OfficerID,@reimburse,@posttest,@pqcdate,@pqcscore) END
My query wants to insert new supplier if there is any. And it should ignore, if the supplier is already present in the table. But it is trying to insert the supplier which is already available. For example, I have PART A with 2 suppliers ABC and DEF. I am getting data from third party for PART A with supplier DEF. As per the condition, it should ignore the record because DEF is already available . But my query is trying to insert supplier DEF and following that, I am getting primary constraint error.
-- Inserting new preferred supplier into R5CATALOGUE
DECLARE @DATEPROCESS DATETIME; SET @DATEPROCESS = CAST(DATEADD(D, -((DATEPART(WEEKDAY, GETDATE()) + 1 + @@DATEFIRST) % 7), GETDATE()) AS DATE) INSERT INTO R5CATALOGUE(CAT_PART, CAT_SUPPLIER,CAT_GROSS,CAT_LEADTIME,CAT_PURUOM,CAT_REF,CAT_MULTIPLY,CAT_CURR,CAT_SUPPLIER_ORG, CAT_PART_ORG,CAT_DESC,CAT_MINORDQTY)
Table TimeRange id binary(16), startTime datetime, endTime datetime, isValid tinyint
There are two validation rules: starttime cannot be null, endTime cannot be null (assume that we cannot set the columns as NOT NULL).
We would like to OUTPUT an error record for each validation error for every record in the TimeRange table.
Would there be a single statement that could do this ? (ie. would UPDATE invalid record AND would OUTPUT two validation error records for a record that has startTime = NULL AND endTime = NULL)
Something like:
UPDATE TimeRange SET isValid = 0 OUTPUT inserted.id, CASE WHEN inserted.startTime is NULL THEN inserted.startTime WHEN inserted.endTime is NULL THEN inserted.endTime END -- Needs to handle the case where both startTime and endTime are invalid INTO @InvalidRecords FROM (a SELECT stmt that is a table with a record for each validation error)
MERGE does not have the functionality needed (inserting multiple records for every invalid record).
Have not had success using a UNION ALL, as there is an error updating derived tables.
I am attempting to create a simple recordset that would return thenumber of duplicates that exist in a table with a single column. Forexample if I had a table like the following:ID Reference Amount1 123456 1.002 123456 2.003 123 1.00I would like to get the following result:ID Reference Amount RecCount1 123456 1.00 22 123456 2.00 23 123 1.00 1Please help!Thanks,Shawn
Can anyone help me to write a query to show customers who have duplicate accounts with Email address, first name, and last name. this is the table structure is Customer table
customerid(PK) accountno fname lname
Records will be
like this
customerid accountno fname lastname 1 2 lori taylor 2 2 lori taylor 3 1 randy dave
How can I made a query to show only my duplicate records ? For some reason that i do not know, i have duplicate entries in my clustered index 21 duplicate records in a table how can i query to know those 21 duplicate records ?
Hello, I have the following Query: 1 declare @StartDate char(8)2 declare @EndDate char(8)3 set @StartDate = '20070601'4 set @EndDate = '20070630'5 SELECT Initials, [Position], DATEDIFF(mi,[TimeOn],[TimeOff]) AS ProTime6 FROM LogTable WHERE 7 [TimeOn] BETWEEN @StartDate AND @EndDate AND8 [TimeOff] BETWEEN @StartDate AND @EndDate9 ORDER BY [Position],[Initials] ASC The query returns the following data: Position Initials ProTime -------------------------------------------------- -------- ----------- ACAD JJ 127 ACAD JJ 62 ACAD KK 230 ACAD KK 83 ACAD KK 127 ACAD TD 122 ACAD TJ 127
What I'm having trouble with is the fact that I need to return a results that has the totals for each set of initials for each position. For Example, the final output that I'm looking to get is the following: Postition Initials ProTime ACAD JJ 189ACAD KK 440ACAD TD 122ACAD TJ 127 Any assistance greatly appreciated.
Query should only return less than 3000 records but its returning over 4M. It needs to show all duplicates records.... All the info are on the same table VENDFIl, so I used a self join but it seems to be looping..
SELECT A.FEDTID, B.VENDOR, C.NPI_NUMBER FROM VENDFIL A, VENDFIL B, VENDFIL C GROUP BY A.FEDTID, B.VENDOR
I have a SQL query I need to design to select name and email addressesfor policies that are due and not renewed in a given time period. Theproblem is, the database keeps the information for every renewal inthe history of the policyholder.The information is in 2 tables, policy and customer, which share thecustid data. The polno changes with every renewal Renewals in 2004would be D, 2005 S, and 2006 L. polexpdates for a given customer couldbe 2007-03-21, 2006-03-21, 2005-03-21, and 2004-09-21, with polno of1234 (original policy), 1234D (renewal in 2004), 1234S (renewal in2005), and 1235L (renewed in 2006).The policy is identified in trantype as either 'rwl' for renewal, or'nbs' for new business.The policies would have poleffdates of 2004-03-21 (original 6 monthpolicy) 2004-09-21 (first 6 month renewal) , 2005-03-21 (2nd renewal,1 year), 2006-03-21(3rd renewal, 1 yr).I want ONLY THE LATEST information, and keep getting earlyinformation.My current query structure is:select c.lastname, c.email, p.polno, p.polexpdatefrom policy p, customer cwhere p.polid = c.polidand p.polexpdate between '2006-03-01 and 2006-03-31and p.polno like '1234%s'and p.trantype like 'rwl'and c.email is not nullunionselect c.lastname, c.email, p.polno, p.polexpdatefrom policy p, customer cwhere p.polid = c.polidand p.polexpdate between '2006-03-01 and 2006-03-31and p.polno like '1234%'and p.trantype like 'nbs'and c.email is not nullHow do I make this query give me ONLY the polno 123%, or 123%Sinformation, and not give me the information on policies that ALSOhave 123%L policies, and/ or renewal dates after 2006-03-31?Adding a 'and not polexpdate > 2006-03-31' does not work.I am working with SQL SERVER 2003. Was using SQL Server 7, but foundit was too restrictive, and I had a valid 2003 licence, so I upgraded,and still could not do it (after updating the syntax - things likeusing single quotes instead of double, etc)I keep getting those policies that were due in the stated range andHAVE been renewed as well as those which have not. I need to get onlythose which have NOT been renewed, and I cannot modify the database inany way.*** Free account sponsored by SecureIX.com ****** Encrypt your Internet usage with a free VPN account from http://www.SecureIX.com ***
I'm trying to do some analysis on duplicate records based off of several match keys. I have a data set of approximately 30,000 people and the goal is to determine how many duplicate matches are in the system.
How would I write an SQL statement that looks for the following pieces of information. (I'm not using one person as an example; I need to do an analysis on the entire data set)
First name (exact match) Last name (exact match) Address line 1 (exact match) Postal code/zip (exact match)
First Initial (exact match) Last name (exact match) DOB exact match Postal code/zip (exact match)
I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria
Generating the 4 lines is not the issue; I call 3 functions to do that together with cross apply.One function to get all dates between the start and end date (dbo.AllDays returning a table with only a datevalue column); one function to have these dates evaluated against a work schedule (dbo.HRCapacityHours) and one function to get the absence records (dbo.HRAbsenceHours) What I can't get fixed is having the correct hours per line.
dear experts, The code written below doesnt report any error and doest serve the objective of updating the record in the database. Can you please suggest what can be the problem? Public Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim conLath As SqlConnection Dim comLath As SqlCommand Dim upcmd conLath = New SqlConnection("Data Source=heybabe1;Initial Catalog=sexsg;User ID=sa;Password=password") conLath.Open() upcmd = "Update Hist set sett_price=@s_p, post_close=@p_s, vol_oq=@v_oq where hist_id=@hid" comLath = New SqlCommand(upcmd, conLath) comLath.Parameters.Add("@s_p", Data.SqlDbType.Decimal, 8) comLath.Parameters.Add("@p_s", Data.SqlDbType.Decimal, 8) comLath.Parameters.Add("@v_oq", Data.SqlDbType.Int, 8) comLath.Parameters.Add("@hid", Data.SqlDbType.Int, 8) comLath.Parameters("@s_p").Value = IIf(sett_price.Text = "", DBNull.Value, sett_price.Text) comLath.Parameters("@p_s").Value = IIf(post_close.Text = "", DBNull.Value, post_close.Text) comLath.Parameters("@v_oq").Value = IIf(vol_oq.Text = "", DBNull.Value, vol_oq.Text) comLath.Parameters("@hid").Value = Request.QueryString("hid") Try comLath.ExecuteNonQuery() Response.Redirect("admindata.aspx") Catch ex As SqlException If ex.Number = 2627 Then Message.InnerHtml = "ERROR: A record already exists with " _ & "the same primary key" Else ' Message.InnerHtml = "ERROR: Could not update record, please ensure the fields are correctly filled out" Message.InnerHtml = ex.Number Message.Style("color") = "red" End If End Try comLath.Dispose() conLath.Close() End Sub
I have a stored procedure that I give some parameters and it joins a couple of tables and returns the results. I would like to know how to update the records in one of the original tables before returning results. CREATE Procedure dbo.DS_GetSomething(@someValue char(5)AS Select table1.* ,table2.* FROM table1 left inner join table1.itemid on table2.itemid where table2.someValue = @someValue The table has a field of IMPRESSIONS that I would like to do something like: ;UPDATE table1 set impressions=impressions + 1 where recordid=?? I don't know how to access the recordids I just got from the original select statement. Thanks for any help Greg
I need to identify duplicate records in a table. TableA [ id, firstname, surname] I’d like to see records that may be duplicates, meaning both firstname and surname are the same and would like to know how many times they appear in the table
I’m not sure how to write this query, can someone help? Thanks in advance!
This is part of my trigger on table T1. I am trying to check if the records inserted to T1 is available in myDB.dbo.myTable or not (destination table). If it is available rollback T1. It does not do that although I insert the same records twice.
-- duplicate record check SET @step = 'Duplicate record' IF EXISTS ( SELECT i.myID, i.Type FROM INSERTED i INNER JOIN myDB.dbo.myTable c ON i.myID = c.myID GROUP BY i.myID, i.Type HAVING (COUNT(*) > 1) AND (i.Type = 'In') ) BEGIN ROLLBACK transaction RAISERROR('Error: step: %s. rollback is done.', 16, 1, @step) Return END
Hi EverybodyThis Code duplicate the record in the database, can somebody help me understand why that happen. Thanks a LOT CompanyName: <asp:textbox id="txtCompanyName" runat="server" /><br />Phone:<asp:textbox id="txtPhone" runat="server" /><br /><br /><asp:button id="btnSubmit" runat="server" text="Submit" onclick="btnSubmit_Click" /><asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:dsn %>" insertcommand="INSERT INTO [items] ([smId], [iTitleSP]) VALUES (@CompanyName, @Phone)" selectcommand="SELECT * FROM [items]"> <insertparameters> <asp:controlparameter controlid="txtCompanyName" name="CompanyName" /> <asp:controlparameter controlid="txtPhone" name="Phone" /> </insertparameters></asp:sqldatasource> VBPartial Class Default2 Inherits System.Web.UI.Page Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click SqlDataSource1.Insert() End SubEnd Class ----------------------------------------------Yes is an Identity the Primary Key of the Table items
In order to check that a new users ID does not already exist in the database I thought it would be a good idea to put the Insert into a Try Catch statement so that I can test for the duplicate record exception and inform the user accordingly. I was also trying to avoid querying the data base before executing the Insert.
The problem is what to actually test for. When the code throws the exception it is a big long string . .
"Violation of PRIMARY KEY constraint 'PK_Users_2__51'. Cannot insert duplicate key in object 'Users'"
I just thought that there has to be something simplar to test for than comparing the exception to the above string.
Can anyone tell me of a better way of doing this ?
(by the way I am only using Web Matrix and MSDE in case it matters)
I am working on a web application that utilizes a sql server database. One of the tables is a large text file that is imported through a DTS package from a Unix server. For whatever reason, the Unix box dumps quite a few duplicate records in the nightly run and these are in turn pulled into the table. I need to get rid of these duplicates, but can't seem to get a workable solution. the query that is needed to get the records is:SELECT tblAppointments.PatientID, tblPTDEMO2.MRNumber, tblAppointments.PatientFirstName, tblAppointments.PatientLastName, tblAppointments.PatientDOB, tblAppointments.PatientSex, tblAppointments.NewPatient, tblAppointments.HomePhone, tblAppointments.WorkPhone, tblAppointments.Insurance1, tblPTDEMO2.Ins1CertNmbr, tblAppointments.Insurance2, tblPTDEMO2.Ins2CertNmbr, tblAppointments.Insurance3, tblPTDEMO2.Ins3CertNmbr, tblAppointments.ApptDate, tblAppointments.ApptTimeFROM tblAppointments CROSS JOIN tblPTDEMO2WHERE (tblAppointments.PatientID = tblPTDEMO2.MRNumber)AND tblAppointments.Insurance1 = 'MED'AND tblAppointments.ApptTypeID <> 'MTG'AND tblAppointments.ApptTypeID <> 'PNV'AND DateDiff("dd", ApptDate, GetDate()) = 0Order By tblAppointments.ApptDateMy first thought was to try to get a Select DISTINCT to work, but couldn't figure out how to do this with the query. My next thought was to try to set up constraints on the table, but, since there are duplicates, the DTS package fails. I assume there is a way to set up the transformations in a way to get this to work, but I'm not enough of an expert with SQL Server to figure this out on my own. I guess the other way to do this is to write some small script or application to do this, but I suspect there must be an easier way for those who know what they are doing. Any help on this topic would be greatly appreciated. Thanks.
So I'm working on updating and normalizing an old database, and I have some duplicate records that I can't seem to get rid of. Every column is identical, right down to what is supposed to be the key. I can't right a delete query to just isolate one row, and I can't delete (or even udpate) any row in management studio. Any thoughts on how to remove the extra rows?
There is a field that's supposed to be unique, so I can write a simple query to get all of the problem rows. The only thing is that they come back in pairs.