Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 4 of 4 total |
SQL to remove value from 'duplicated' records |
Thu, Apr 2 2009 12:03 AM | Permanent Link |
"Adam H." | 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. |
Thu, Apr 2 2009 4:36 AM | Permanent Link |
"John Hay" | 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 |
Thu, Apr 2 2009 4:38 AM | Permanent Link |
"John Hay" | Adam
oops, missed a group by SELECT Field1,MIN(RecordId) AS Rec INTO Memory\Temp FROM Table GROUP BY Field1; John |
Thu, Apr 2 2009 5:50 PM | Permanent Link |
"Adam H." | Good Morning John,
Thanks so much for taking the time out to reply with your posts - works a treat! Cheers Adam. |
This web page was last updated on Tuesday, May 7, 2024 at 06:25 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |