2 Replies - 752 Views - Last Post: 15 February 2012 - 07:28 PM Rate Topic: -----

#1 superkb10  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 298
  • Joined: 27-November 11

Deleting only one record from Table

Posted 15 February 2012 - 05:40 PM

Hey all, I'm writing a C# application, but it'll need some SQL coding in it. I need to be able to delete a single record from a table. So using the DELETE statement but making it so that if there are multiple records of the same value, it only deletes one of them. For example, let's say I have the following code:


DELETE FROM myTable WHERE myColumn = 'text'




But I have five different records where to value is "text". How can I make it so that I delete only one of them(doesn't matter which). I've heard there is a way to do this, I just don't know how.

Is This A Good Question/Topic? 0
  • +

Replies To: Deleting only one record from Table

#2 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 801
  • View blog
  • Posts: 1,700
  • Joined: 30-January 09

Re: Deleting only one record from Table

Posted 15 February 2012 - 06:28 PM

At the risk of making an ass out of u and umption, I'll make the assumption that you are using MSSQL, and not MySQL. The syntax between the two is slightly different, so if you are using something other than MSSQL (e.g. MySQL, Oracle, etc.) you should post back here, as the query may need tweaking.

To achieve your result, there are several methods. The third method is the best, but I will walk you through the various methods in any case.

In the first case we'll assume that you have a Primary Key in the table myTable, and it is a column named 'ID'. In this case, you can do the following:
DELETE FROM myTable
WHERE ID IN (
	SELECT TOP (1) ID
	FROM myTable
	WHERE myColumn = 'text'
)


This will delete the first record that matches the clause WHERE myColumn = 'text'. Note that this is the first record that SQL matches - there is no guarantee which record it is out of all of the matches. You may want to delete the earliest record of that match that was entered in the DB, in which case you could add an ORDER clause like so:
DELETE FROM myTable
WHERE ID IN (
	SELECT TOP (1)
		ID
	FROM myTable
	WHERE myColumn = 'text'
	ORDER BY ID
)



In the second case, you would have some sort of composite key as a unique identified. Let's say your composite key is the pair of columns firstName and lastName. In this case, you would need to change your query to this:
DELETE FROM myTable
WHERE CONVERT(nvarchar(1024), firstName) & CONVERT(nvarchar(1024), lastName) IN (
	SELECT TOP (1)
		CONVERT(nvarchar(1024), firstName) & CONVERT(nvarchar(1024), lastName)
	FROM myTable
	WHERE myColumn = 'text'
)


While this is a bad example of a composite key, it is valid example of utilising a composite key to determine a specific row. You could also have used this method, which is arguably a better approach:
DECLARE @Locations table (
	firstName			nvarchar(1024),
	lastName			nvarchar(1024)
);

INSERT INTO @Locations (
SELECT TOP (1)
	firstName,
	lastName
FROM myTable
WHERE myColumn = 'text');

DELETE FROM myTable
WHERE firstName IN (SELECT firstName FROM @Locations)
AND lastName IN (SELECT lastName FROM @Locations);



The third case is the most robust method, and handles the scenario where you don't have a unique identifier. It also handles cases where you do have a unique identifier, so it's very robust. Furthermore it guarantees the deletion of just one record, which the previous methods may not, depending on the uniqueness of the identifiers. The syntax is quite a lot simpler too:
DELETE TOP (1)
FROM myTable
WHERE myColumn = 'text';


Keep in mind that you can also order this query to narrow it down to a specific result a bit better:
DELETE TOP (1)
FROM myTable
WHERE myColumn = 'text'
ORDER BY ID;


Hope this helps.

This post has been edited by e_i_pi: 15 February 2012 - 06:31 PM

Was This Post Helpful? 0
  • +
  • -

#3 superkb10  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 298
  • Joined: 27-November 11

Re: Deleting only one record from Table

Posted 15 February 2012 - 07:28 PM

It helped a lot, thanks! I think I'll do the fifth options seeing as it doesn't matter which one is deleted, just one of the records must be deleted. But the other suggestions will probably help in the future. Thanks!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1