SQL Delete of a single record

[Monty]

Is there a way to code an SQL DELETE statement so that it deletes ONLY
the first record it encounters (that matches the criteria)?

Thanks,
Dale

 

[Dirk Goldgar]

Is there a way to code an SQL DELETE statement so that it deletes ONLY
the first record it encounters (that matches the criteria)?

You have to be able to define which is the "first" record, which means
two things: (1) there has to be a unique key available to identify the
specific record, and (2) you have to supply a sort order that defines
which of several possible matching records is to be considered the
"first" one for your purposes.

If you can meet those requirements, a query might take the following
form:

DELETE FROM MyTable
WHERE KeyField =
(SELECT TOP 1 KeyField FROM MyTable
WHERE SomeCriteriaField="SomeValue"
ORDER BY SomeOrderingField, KeyField);

In the above example,

MyTable is the name of the table

KeyField is the primary key of the table

SomeCriteriaField is a field by which you select
candidates for deletion

SomeOrderingField is a field you use to define
the sort order (to identify which record is "first")

Note that KeyField appears again in the ORDER BY clause. That's to
break ties, in case there are two or more records meeting the criteria
that have the same value in [SomeOrderingField].

 

[Monty]

Is there a way to code an SQL DELETE statement so that it deletes ONLY
the first record it encounters (that matches the criteria)?

You have to be able to define which is the "first" record, which means
two things: (1) there has to be a unique key available to identify the
specific record, and (2) you have to supply a sort order that defines
which of several possible matching records is to be considered the
"first" one for your purposes.

If you can meet those requirements, a query might take the following
form:

DELETE FROM MyTable
WHERE KeyField =
(SELECT TOP 1 KeyField FROM MyTable
WHERE SomeCriteriaField="SomeValue"
ORDER BY SomeOrderingField, KeyField);

In the above example,

MyTable is the name of the table

KeyField is the primary key of the table

SomeCriteriaField is a field by which you select
candidates for deletion

SomeOrderingField is a field you use to define
the sort order (to identify which record is "first")

Note that KeyField appears again in the ORDER BY clause. That's to
break ties, in case there are two or more records meeting the criteria
that have the same value in [SomeOrderingField].

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

That works great, thanks