Reputation: 642
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.
Upvotes: 1
Views: 4849
Reputation: 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 & ")"
Upvotes: -1
Reputation: 24464
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.
Upvotes: 0
Reputation: 476
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
Upvotes: 1
Reputation: 8008
Did you try something like this?
DELETE TableName where IdColumn In ( select Top(1) IdColumn from TableName Where columnName=valuev)
Upvotes: 4
Reputation: 86735
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.
Upvotes: 1