I have a "union" table which results of a union of two tables.
Occasionally I could have duplicates, when the same PIN has been added
to both tables, albeit at different Datees/Times, such as:
PINNameAdded Date
100411A7/11/2007 10:12:58 AM
100411A7/17/2007 10:54:23 AM
100413B7/11/2007 10:13:28 AM
100413B7/17/2007 10:54:39 AM
104229C7/6/2007 2:34:13 PM
104231D7/6/2007 2:34:25 PM
104869E6/10/2007 11:59:12 AM
104869E6/22/2007 2:40:18 PM
The question is - how can I delete by queries the first occurence
(time-wise) of these duplicates - i.e. I would want to delete the
first occurence of 100411 (A), the first occurence of 100413 (B), and
the first occurence of 104869 (E) in the example above - records C and
D show only once, so they are fine.
Is there a MsAccess solution ? Is there a SQL-server solution ?
Some guy posted that the syntax: delete top 1 from some_table works for deleting duplicates. I am pretty sure that doesnt work but I wanted to check just in case it did because it would be a really easy to delete duplicates.
Hi i need a query to check a table and if any duplicates of the column called "MessageID" and if there are any duplicates then delete them leaving just the one unique MessageID
so i have
MessageID, Number, Text.
12,33333333333,hello 12,33333333333,hello - Delete this one 12,33333333333,hello - Delete this one 14,55555555555,new
I'm having trouble figuring out how to delete some _almost_ duplicaterecords in a look-up table. Here's the table:CREATE TABLE [user_fields] ([fKEY] [char] (16) NOT NULL ,[SEQUENCE] [char] (2) NOT NULL ,[FIELD_LABEL] [varchar] (20) NULL ,[FIELD_VALUE] [varchar] (50) NULL ,[EXPORT_DATE] [datetime] NULL ,CONSTRAINT [PK_user_fields] PRIMARY KEY CLUSTERED([fKEY],[SEQUENCE])CONSTRAINT [FK_USRFLD_INV_DOCID] FOREIGN KEY([fKEY]) REFERENCES [OTHER_TABLE] ([PKEY]))Some values:fKEY SEQUENCE FIELD_LABEL FIELD_VALUE----------------------------------------------------------8525645200692B8919Co. ID #8525645200692B8920Co. ID #8525645200692B8921Co. ID #8525645200692B8913Co/Div/Dept8525645200692B8914Co/Div/Dept8525645200692B8915Co/Div/Dept8525645200692B8916Division8525645200692B8917Division8525645200692B8918Division8525645200692B8910Group8525645200692B8911Group8525645200692B8912Group8525645200692B891 HR ContactJOHN NOVAK8525645200692B892 HR ContactJOHN NOVAK8525645200692B893 HR ContactJOHN NOVAK8525645200692B8924Job Location8525645200692B8922Job Location8525645200692B8923Job Location8525645200692B894 Manager8525645200692B895 Manager8525645200692B896 Manager8525645200692B897 Recruiter8525645200692B898 Recruiter8525645200692B899 Recruiter85256D740081C3A413Co. ID #85256D740081C3A414Co. ID #85256D740081C3A410Co/Div/Dept85256D740081C3A49 Co/Div/Dept85256D740081C3A411Division85256D740081C3A412Division85256D740081C3A48 Group85256D740081C3A47 Group85256D740081C3A42 HR ContactDiana Tarry85256D740081C3A41 HR ContactDiana Tarry85256D740081C3A415Job Location85256D740081C3A416Job Location85256D740081C3A43 Manager85256D740081C3A44 Manager85256D740081C3A45 Recruiter85256D740081C3A46 RecruiterNote that fKEY 8525645200692B89 has three of every FIELD_LABEL, andfKEY 85256D740081C3A4 has two. Both, however, should have only one.Unfortunately, when I do a slect ... having count(*) > 1, I have nearly900 different fKEYs with some variation of this problem.It's just not coming to me how to delete the duplicates (except forsequence). I don't care which of the sequence values I keep but as amatter of preference I tried to do something using max(sequence) but,so far, everything I've tried deletes all records for any given fKEY.Help?Thanks.Randy
I'm trying to clean up a database design and I'm in a situation to where two tables need a FK but since it didn't exist before there are orphaned records.
Tables are:
Brokers and it's PK is BID
The 2nd table is Broker_Rates which also has a BID table.
I'm trying to figure out a t-sql statement that will parse through all the recrods in the Broker_Rates table and delete the record if there isn't a match for the BID record in the brokers table.
I know this isn't correct syntax but should hopefully clear up what I'm asking
by executing this qry i will get below result ,in that TId is duplicates that is from that i want recent record with same result.i want last one if TId comes duplicate record. any one ans pl
select sa.GrandTotal, sa.TId, sa.Id,sa.Date, pr.PName, pr.PCode from Sales sa Left Outer Join Product pr on sa.Pid = pr.Id where sa.GrandTotal is not null
Hi everybody I need help on finding duplicates and deleting the duplicate record depending on name and fname , deleting the duplicates and leaving only the first one.
my PERSON table is this below:
ID name fname ownerid id2
1 a b 2 c c 3 e f 4 a b 1 10 5 c c 2 11
I have this query below that returns records 1 and 4 and 2 and 5 since they have the same name and fname
select * from ( Select name ,fname, count(1) as cnt from PERSON group by name,Fname ) where cnt > 1
ID name fname ownerid id2
1 a b 4 a b 1 10
2 c c 5 c c 2 11
With this result I need to delete the second record of each group but update the first records with the ownerid and id2 of the second record that would be deleted... I don't know how to proceed with this..
I have a DELETE statement that deletes duplicate data from a table. Ittakes a long time to execute, so I thought I'd seek advice here. Thestructure of the table is little funny. The following is NOT the table,but the representation of the data in the table:+-----------+| a | b |+-----+-----+| 123 | 234 || 345 | 456 || 123 | 123 |+-----+-----+As you can see, the data is tabular. This is how it is stored in the table:+-----+-----------+------------+| Row | FieldName | FieldValue |+-----+-----------+------------+| 1 | a | 123 || 1 | b | 234 || 2 | a | 345 || 2 | b | 456 || 3 | a | 123 || 3 | b | 234 |+-----+-----------+------------+What I need is to delete all records having the same "Row" when there existsthe same set of records with a different (smaller, to be precise) "Row".Using the example above, what I need to get is:+-----+-----------+------------+| Row | FieldName | FieldValue |+-----+-----------+------------+| 1 | a | 123 || 1 | b | 234 || 2 | a | 345 || 2 | b | 456 |+-----+-----------+------------+A slow way of doing this seem to be:DELETE FROM XWHERE Row IN(SELECT DISTINCT Row FROM X x1WHERE EXISTS(SELECT * FROM X x2WHERE x2.Row < x1.RowAND NOT EXISTS(SELECT * FROM X x3WHERE x3.Row = x2.RowAND x3.FieldName = x2.FieldNameAND x3.FieldValue <> x1.FieldValue)))Can this be done faster, better, and cheaper?
Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray
I have a table employee_test having the sample data. The rows with EmployeeID=6 are duplicate rows. I want to delete the duplicates retaining one row for the employeeid=6. Note :- I don't want to use a temporary table. I want to do this using a single query or at the most in a SP query batch. Please advise.
I have a query which finds duplicate spec_items linked to a work order. What I want to do it remove the duplicates (and in some cases there will be more than one) leaving only the record with the highest [sr.id]
select sr.id, sr.linked_to_worknumber, sr.spec_checklist_id from spec_checklist_remind sr inner join spec_checklist_remind sc on sc.linked_to_worknumber = sr.linked_to_worknumber group by sr.id,sr.linked_to_worknumber, sr.spec_checklist_id Having sr.spec_checklist_id = 30 and count(*)>1 order by sr.linked_to_worknumber
Auto_ID Account_ID Account_Name Account_Contact Priority 1 3453463 Tire Co Doug 1 2 4363763 Computers Inc Sam 1 3 7857433 Safety First Heather 1 4 2326743 Car Dept Clark 1 5 2342567 Sales Force Amy 1 6 4363763 Computers Inc Jamie 2 7 2326743 Car Dept Jenn 2
I'm trying to delete all duplicate Account_IDs, but only for the highest priority (in this case it would be the lowest number).
I know the following would delete duplicate Account_IDs:
DELETE FROM staging_account WHERE auto_id NOT IN (SELECT MAX(auto_id) FROM staging_account GROUP BY account_id)
The problem is this doesn't take into account the priority; in the above example I would want to keep auto_ids 2 and 4 because they have a higher priority (1) than auto_ids 6 and 7 (priority 2).
How can I take priority into account and still remove duplicates in this scenario?
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.
How can i delete a record from a table . table contains approx 3 million data and when i execute a query for delete it give error message from transaction log space problem. Is there any way to stop the log in transaction log and delete the record.
whats the best way to delete a parent record? for now what i am doing is to have a special delete page (means not DetailsView/GridView) just to delete parent records so user can see what child records will be deleted too. is the a better way?my code below: The T-SQL Stored ProcedureALTER PROCEDURE dbo.DeleteInventory ( @InventoryID int ) AS DELETE FROM Booking WHERE InventoryID = @InventoryID
DELETE FROM Inventory WHERE InventoryID = @InventoryID The code in the Code Behind to execute when a button(delete) click Try Dim temp As Integer = SqlDataSource4.Delete If temp > 0 Then lblDeleteDtatus.ForeColor = Drawing.Color.Blue lblDeleteDtatus.Text = temp & " Records/Rows Deleted." Else lblDeleteDtatus.ForeColor = Drawing.Color.Orange lblDeleteDtatus.Text = "No records Deleted!" End If Catch ex As Exception lblDeleteDtatus.ForeColor = Drawing.Color.Red lblDeleteDtatus.Text = "An Error Occured.<br />" & ex.Message.ToString End Try any better methods anyone wants to share?
This one is driving me nuts. I've issued a very simple statement to delete a single row from a table. It appears that when I execute it in SQL Query Analyzer the CPUTime spikes and holds one of the CPUs on the box pegged at 100%. I've let this thing run for over a day, and it's not deleting the one damn record. Any thoughts? :confused: :confused: Here's the command I'm executing:
DELETE FROM Invoices WHERE InvoiceID = 153345
Running SELECT * FROM Invoices WHERE InvoiceID = 153345 returns only a single record as it should. InvoiceID is the PK in this table. Any and all help is greatly appreciated. I've rebooted the server, but to no avail. Same thing happens after a reboot.
Hi everyone... I have a problem on deleting a record. I accidentally duplicate the record. I need to delete other records and left only 1 record based on each date and employeenumber.
supposing i have a table hrempshiftschedule i have 3 fields... empno, shiftdate, and shifttype
Hi i have a very annying problem that i cant seem to solve by myself. I have developed a content managment system for a webpage where people can manage the page. It's almost done except for the fact that i cant seem to delete records from my sql express database. To access the database i use an sql login in my code to delete witchever record is retrived from a querystring in the URL field. Below is a sample of my delete code when a button is pressed. protected void ButtonDelete_Click(object sender, EventArgs e) { string dID = Request.QueryString["dID"];
string myConnectionString = @"Data Source=SRVWEBSQLEXPRESS;Initial Catalog=se;User ID=xx;Password=xx"; SqlConnection myConnection = new SqlConnection(myConnectionString); string myDeleteQuery = "DELETE FROM drift WHERE dID = @dID"; SqlCommand myCommand = new SqlCommand(myDeleteQuery); myCommand.Parameters.AddWithValue("dID", dID);
Response.Redirect("list.aspx"); } When the i run the code on my development machine located at 10.12.0.80 and the server is located at 10.12.1.65 and this is where the databse is located. The strangest thing is that when i press my deletebutton while debugging in VS2008 on my devmachine the record is deleted! BUT when i run the code live on the server i get an error. See the below log file taken from the windows 2003 server application log. Event Type: InformationEvent Source: ASP.NET 2.0.50727.0Event Category: Web Event Event ID: 1314Date: 2008-04-07Time: 10:26:45User: N/AComputer: SRVWEBDescription:Event code: 4011 Event message: An unhandled access exception has occurred. Event time: 2008-04-07 10:26:45 Event time (UTC): 2008-04-07 08:26:45 Event ID: 8bdda96aeee44448b570891c593bdb3e Event sequence: 242 Event occurrence: 1 Event detail code: 0 Application information: Application domain: /LM/W3SVC/1015505475/Root-1-128520196339603398 Trust level: Full Application Virtual Path: / Application Path: C:wwwwebsite Machine name: SRVWEB Process information: Process ID: 5156 Process name: w3wp.exe Account name: NT AUTHORITYNETWORK SERVICE Request information: Request URL: http://website/cms/drift/editdrift.aspx?dID=19 Request path: /cms/drift/editdrift.aspx User host address: 10.12.1.1 User: webmaster Is authenticated: True Authentication Type: Forms Thread account name: NT AUTHORITYNETWORK SERVICE Could anyone help me solve this problem. Thanks.
If I script the SQL statement with a constant, deleting the record from the database works.
If I script the SQL statement to delete based on the WHERE clause being a variable name, it will not delete the record.
The value being compared in the WHERE clause comes directly from the Sequel database.
I have a dropdown box that is filled from the database. The dropdown1.selecteditem.text is placed in a variable. The script is to delete a record from the database where the table.name in the database equals the item name selected from the dropdown box.
This querystring does not delete the record from the database:
dim queryString As String = "DELETE FROM [Table1] WHERE ([Table1].[name] = 'variableCompareText')"
This querystring does delete the recrod from the database:
dim queryString As String = "DELETE FROM [Table1] WHERE ([Table1].[name] = 'John')"
Why can't I delete a record with a variable in the querystring? Otherwise, you would have to always know in advance which record to specify rather than being deleted dynamically.
I'm having trouble with the below sql command. What I'm trying to do is to delete records from tblPhotoHeader table where there are no corresponding child records in tblPhoto.
The select statement works correctly, I'm just not sure about how to apply the syntax to correctly select the records in the delete statement.
Any help from the experts here would be helpful.
Thanks
Tom
--------------------------------------------------------------------- DELETE FROM tblPhotoHeader WHERE Exists
Well, I really messed up. Instead of changing the name of a current company record in a table I changed ALL the company names in the table. Me.CustomerDataSource.SelectCommand = "UPDATE tbl_customers SET company = '" & companyTextBox.Text & "'" So, I need to insert a WHERE clause to fix this. My problem is that I've been searching everywhere for this simple command structure and cannot find anything that specifically addresses a simple way to reference the current record. I tried...Me.CustomerDataSource.SelectCommand = "UPDATE tbl_customers SET company = '" & companyTextBox.Text & "' WHERE recno = @recno" But I get the error: Exception Details: System.Data.SqlClient.SqlException: Must declare the scalar variable "@recno". Can anyone provide this simple query clause?
I have a table with a large number of records that I need to delete, before attempt to perform the delete I also archived the records to another table.
So I need to delete all of these selected records stored in the archive table from the main table. I can now reference all the records that qualify for the delete in the main table by performing a join on the archive table like so:
select * from my_main_table a join my_archive_table b on a.distinct_id=b.distinct_id and a.surrogate_key=b.surrogate_key and a.identifier=b.indentifier
So all the records check out to be the ones I'd like to perform a delete on but I just can't figure out how to perform a delete of the records with little or no change to the existing query.
Obviously something like this won't work:
delete from my_main_table a join my_archive_table b on a.distinct_id=b.distinct_id and a.surrogate_key=b.surrogate_key and a.identifier=b.indentifier
Though it would be nice if it did.:D
So my question is how would I use the existing query with some modification to delete only the records that this query returns. I've tried selection of records in the main table based on the existing records in the archive table but it can return a higher number of records than what I know is expected. I actually need the join specified to be in place to do it.
