1

select Top(1)* from TableName Where columnName=value

selects only the first row just fine. However if I change the select to a delete I get an error and can't figure out how to write a query to delete only 1 record that matches my query from the db.

I'm wondering if anybody out there smarter than I knows if or how this can be done in SQL CE.

Shaun Poore
  • 612
  • 11
  • 23

5 Answers5

4

Did you try something like this?

DELETE TableName where IdColumn In ( select Top(1) IdColumn from TableName Where columnName=valuev)
Sebastian Piu
  • 7,838
  • 1
  • 32
  • 50
  • +1 That's another good option too. I'd play with as many options as possible if you're unfamiliar (like me) as to the Exact restrictions that exist in CE. – MatBailie Dec 08 '11 at 22:41
  • currently that table doesn't have a key. I could redesign the table and some application logic and in all likelyhood will have to do that if SQL Server CE doesn't support what I want to do – Shaun Poore Dec 08 '11 at 22:51
  • see the accepted answer here then: http://stackoverflow.com/questions/439750/t-sql-selecting-rows-to-delete-via-joins – Sebastian Piu Dec 08 '11 at 22:53
  • sorry, ignore that, i forgot about the top 1. How do you identify what you want to delete then? or is just a random top 1 and you dont care? – Sebastian Piu Dec 08 '11 at 22:56
  • The existence of a unique identifier (be it a composite key, or otherwise) is a very good practice to get into. Go with your own suggestion on that one :) – MatBailie Dec 08 '11 at 22:57
  • Ya I don't care which one gets deleted I just want a single record to be deleted. I'm going to put a key in there and give your answer a shot. – Shaun Poore Dec 08 '11 at 23:03
  • Awesome with the key your answer works great. Noted about having a key in every table. – Shaun Poore Dec 08 '11 at 23:18
1

I don't know specifically as I'm at home, but SQL CE is deliberately restricted in what it can do. One reason for this is that it is 'always' running locally to the process referencing it.

What means is that it is Expected that the other process is expected to handle much of the logic that may otherwise be encapsulated in the SQL Server. This often results in firing several queries at the SQL CE instance, where you may be more accustomed to firing off one.

In this case, you could do it with two queries...
1) A query to identify the record that you want to delete
2) Use that Identifier in another query to do the actual delete


You could also try using SET ROWCOUNT 1 to limit the DELETE to just 1 row. But again, I don't know if that works in CE.

MatBailie
  • 83,401
  • 18
  • 103
  • 137
1

You can use CTE such as

;with myTopRow(rowID)
(
    select Top 1 rowID from TableName Where columnName=value
)
delete from TableName inner join myTopRow on TableName.rowID = myTopRow.rowID
hshen
  • 468
  • 1
  • 4
  • 13
0

Shouldn't it be rather :

DELETE FROM TableName WHERE columnName=value ORDER BY columnName LIMIT 1;

IMHO, the table logically has NO order by itself. It has it physically, but you can't rely on it. So, you HAVE to set the order in which you want to delete the first row.

Gangnus
  • 24,044
  • 16
  • 90
  • 149
-1

The following code will delete only first row

Dim mySqlCommondDelete As String = "DELETE BOOK_ID, MemberID FROM (SELECT TOP 1 * FROM ISSUE_BOOK) where BOOK_ID = Val(" & deleteBook & ") and MemberID = Val(" & msk & ")"
Matas Vaitkevicius
  • 58,075
  • 31
  • 238
  • 265
pubudu
  • 1