SQ-heLp

[JonWayn]

can somebody help me out with an SQL statement that will update the [Status]
field to 1 for the FIRST [Defendant] of each (duplicate) group, sorted on
the [Priority] field?

In other words, I have a table with fields Defendant, Status, Priority, and
others. Every [Defendant] is duplicated a random amount of times. After
sorting on the [Priority] field (in ascending) order, I'd like to select
only the first record of each defendant group to update the [Status] field
of that record to the value 1. How do I word such an SQL statement? I tried
using the FIRST predigate but I ended up selecting only a single record no
matter how many duplicate groups exist in the table.

Thank you

 

[Dirk Goldgar]

can somebody help me out with an SQL statement that will update the
[Status] field to 1 for the FIRST [Defendant] of each (duplicate)
group, sorted on the [Priority] field?

In other words, I have a table with fields Defendant, Status,
Priority, and others. Every [Defendant] is duplicated a random amount
of times. After sorting on the [Priority] field (in ascending) order,
I'd like to select only the first record of each defendant group to
update the [Status] field of that record to the value 1. How do I
word such an SQL statement? I tried using the FIRST predigate but I
ended up selecting only a single record no matter how many duplicate
groups exist in the table.

Thank you

Is there a unique ID field that an be used to identify a specific record
in the table? I'll assume for demonstration purposes that there is, and
that the field is called "CaseID". Do you want to do this only for
those Defendants for which duplicates exist, or would a unique Defendant
also be assigned a Status of 1? If the latter, something along these
lines ought to do it (assuming a table named "tblCases"):

UPDATE tblCases SET tblCases.Status = 1
WHERE tblCases.CaseID=
(SELECT TOP 1 T.CaseID FROM tblCases AS T
WHERE T.Defendant = tblCases.Defendant
ORDER BY T.Priority);

 

[JonWayn]

No, there is no identity field in the table.
All defendants in this table have duplicates in the Defendant field: this
table is actually a duplicate extraction from a larger table.

can somebody help me out with an SQL statement that will update the
[Status] field to 1 for the FIRST [Defendant] of each (duplicate)
group, sorted on the [Priority] field?

In other words, I have a table with fields Defendant, Status,
Priority, and others. Every [Defendant] is duplicated a random amount
of times. After sorting on the [Priority] field (in ascending) order,
I'd like to select only the first record of each defendant group to
update the [Status] field of that record to the value 1. How do I
word such an SQL statement? I tried using the FIRST predigate but I
ended up selecting only a single record no matter how many duplicate
groups exist in the table.

Thank you

Is there a unique ID field that an be used to identify a specific record
in the table? I'll assume for demonstration purposes that there is, and
that the field is called "CaseID". Do you want to do this only for
those Defendants for which duplicates exist, or would a unique Defendant
also be assigned a Status of 1? If the latter, something along these
lines ought to do it (assuming a table named "tblCases"):

UPDATE tblCases SET tblCases.Status = 1
WHERE tblCases.CaseID=
(SELECT TOP 1 T.CaseID FROM tblCases AS T
WHERE T.Defendant = tblCases.Defendant
ORDER BY T.Priority);

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

(please reply to the newsgroup)

 

[Dirk Goldgar]

No, there is no identity field in the table.
All defendants in this table have duplicates in the Defendant field:
this table is actually a duplicate extraction from a larger table.

can somebody help me out with an SQL statement that will update the
[Status] field to 1 for the FIRST [Defendant] of each (duplicate)
group, sorted on the [Priority] field?

In other words, I have a table with fields Defendant, Status,
Priority, and others. Every [Defendant] is duplicated a random
amount of times. After sorting on the [Priority] field (in
ascending) order, I'd like to select only the first record of each
defendant group to update the [Status] field of that record to the
value 1. How do I word such an SQL statement? I tried using the
FIRST predigate but I ended up selecting only a single record no
matter how many duplicate groups exist in the table.

Thank you

Is there a unique ID field that an be used to identify a specific
record in the table? I'll assume for demonstration purposes that
there is, and that the field is called "CaseID". Do you want to do
this only for those Defendants for which duplicates exist, or would
a unique Defendant also be assigned a Status of 1? If the latter,
something along these lines ought to do it (assuming a table named
"tblCases"):

UPDATE tblCases SET tblCases.Status = 1
WHERE tblCases.CaseID=
(SELECT TOP 1 T.CaseID FROM tblCases AS T
WHERE T.Defendant = tblCases.Defendant
ORDER BY T.Priority);

There needs to be some combination of fields in the table that uniquely
identifies a record. What fields would these be? Is Defendant+Priority
unique? If there is no such combination of fields, then your question
cannot be answered without first adding a unique key.

 

[JonWayn]

Defendant, Offense, CSType make a record unique

No, there is no identity field in the table.
All defendants in this table have duplicates in the Defendant field:
this table is actually a duplicate extraction from a larger table.

can somebody help me out with an SQL statement that will update the
[Status] field to 1 for the FIRST [Defendant] of each (duplicate)
group, sorted on the [Priority] field?

In other words, I have a table with fields Defendant, Status,
Priority, and others. Every [Defendant] is duplicated a random
amount of times. After sorting on the [Priority] field (in
ascending) order, I'd like to select only the first record of each
defendant group to update the [Status] field of that record to the
value 1. How do I word such an SQL statement? I tried using the
FIRST predigate but I ended up selecting only a single record no
matter how many duplicate groups exist in the table.

Thank you

Is there a unique ID field that an be used to identify a specific
record in the table? I'll assume for demonstration purposes that
there is, and that the field is called "CaseID". Do you want to do
this only for those Defendants for which duplicates exist, or would
a unique Defendant also be assigned a Status of 1? If the latter,
something along these lines ought to do it (assuming a table named
"tblCases"):

UPDATE tblCases SET tblCases.Status = 1
WHERE tblCases.CaseID=
(SELECT TOP 1 T.CaseID FROM tblCases AS T
WHERE T.Defendant = tblCases.Defendant
ORDER BY T.Priority);

There needs to be some combination of fields in the table that uniquely
identifies a record. What fields would these be? Is Defendant+Priority
unique? If there is no such combination of fields, then your question
cannot be answered without first adding a unique key.

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

(please reply to the newsgroup)

 

[Dirk Goldgar]

Defendant, Offense, CSType make a record unique

No, there is no identity field in the table.
All defendants in this table have duplicates in the Defendant field:
this table is actually a duplicate extraction from a larger table.


can somebody help me out with an SQL statement that will update
the [Status] field to 1 for the FIRST [Defendant] of each
(duplicate) group, sorted on the [Priority] field?

In other words, I have a table with fields Defendant, Status,
Priority, and others. Every [Defendant] is duplicated a random
amount of times. After sorting on the [Priority] field (in
ascending) order, I'd like to select only the first record of each
defendant group to update the [Status] field of that record to the
value 1. How do I word such an SQL statement? I tried using the
FIRST predigate but I ended up selecting only a single record no
matter how many duplicate groups exist in the table.

Thank you

Is there a unique ID field that an be used to identify a specific
record in the table? I'll assume for demonstration purposes that
there is, and that the field is called "CaseID". Do you want to do
this only for those Defendants for which duplicates exist, or would
a unique Defendant also be assigned a Status of 1? If the latter,
something along these lines ought to do it (assuming a table named
"tblCases"):

UPDATE tblCases SET tblCases.Status = 1
WHERE tblCases.CaseID=
(SELECT TOP 1 T.CaseID FROM tblCases AS T
WHERE T.Defendant = tblCases.Defendant
ORDER BY T.Priority);

There needs to be some combination of fields in the table that
uniquely identifies a record. What fields would these be? Is
Defendant+Priority unique? If there is no such combination of
fields, then your question cannot be answered without first adding a
unique key.

It seems to me that the combination of Defendant and Priority must also
be unique; else how would anyone know which of two records with the
same priority for the same defendant should have its status set to 1?
If I'm right about this, I think something like this SQL statement
should do it:

UPDATE tblCases A SET A.Status = 1
WHERE A.Priority =
(SELECT Min(B.Priority) FROM tblCases B
WHERE B.Defendant = A.Defendant);

If I'm wrong about it, please explain how to choose between records with
the same priority for the same defendant.