Solved

Need Qry Syntax Help - Need to delete two records that are exactly the same

Posted on 2007-03-24
3
218 Views
Last Modified: 2010-03-19
I accidentally inserted two records that are exactly the same into a MS SQL (2005) table.  I can't delete them using the following qry:

Delete from Competitors where CompetitorID=66 and SeasonID=12 and EventID=0 and RunID=6

When I run the qry, it says 0 rows affected.  The only way I know how to fix this is truncate the table and then reinsert the data.  Is there another way, because there is no way I want to risk the data.

Thanks in advance!

Regards,
Eric
0
Comment
Question by:indy500fan
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 300 total points
Comment Utility
>Delete from Competitors where CompetitorID=66 and SeasonID=12 and >EventID=0 and RunID=6
>When I run the qry, it says 0 rows affected.
then, your where clause is not correct.

can you please show the query that returns the data of the 2 rows (SELECT)?
I would use the following technique to delete a single row (even if there are several rows):
SET ROWCOUNT 1
DELETE Competitors  WHERE ...
SET ROWCOUNT 0
0
 
LVL 10

Assisted Solution

by:lahousden
lahousden earned 200 total points
Comment Utility
If you are sure that these are the identifying values for these columns for the rows in question then it looks like you don't have the problem you think you have.  Change the "Delete" to a "Select * " in your SQL statement and see whether in fact you still have the troublesome rows in your table, i.e.

Select * from Competitors where CompetitorID=66 and SeasonID=12 and EventID=0 and RunID=6
0
 

Author Comment

by:indy500fan
Comment Utility
angellll,
Well after trying to duplicate, I can't.
Thanks,
Eric
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now