Hi,

I'm not sure how to explain this the correct way, so I'll just shoot.

I have a table with the following fields:

SELECTED    (Boolean)
FIELD1            (String)
FIELD2            (String)

In this table I may have the following data:

True    AAAA    Some Text
True    BBBB    Some Text
True    CCCC    Some Text
True    CCCC    Some Different Text
True    CCCC    Some More Different Text
True    DDDD    Some Text

, etc.

What I would like to do is perform an SQL statement to update the table, so
only the first record for each FIELD has TRUE in the selected field. ie -
the table should look like:

True    AAAA    Some Text
True    BBBB    Some Text
True    CCCC    Some Text
False   CCCC    Some Different Text  <<<<<Changed
False   CCCC    Some More Different Text <<<<<Changed
True    DDDD    Some Text

This way, there should never be two records with the same FIELD1 that has
Selected for TRUE in both of them after the SQL has been updated.

Is this possible to do in SQL?

Thanks & Regards

Adam.

Adam

If there is no single primary key then

UPDATE Table SET selected=false;
SELECT Field1,MIN(RecordId) AS Rec INTO Memory\Temp FROM Table;
CREATE INDEX Ix ON Memory\Temp (Rec);
UPDATE Table SET selected=true WHERE RecordId IN (SELECT Rec FROM
Memory\Temp)

If there is a single primary key replace recordid with the primary key field

Cheers

John

Adam

oops, missed a group by

SELECT Field1,MIN(RecordId) AS Rec INTO Memory\Temp FROM Table GROUP BY
Field1;

John

Good Morning John,

Thanks so much for taking the time out to reply with your posts - works a
treat!

Cheers

Adam.