i need to delete all records where the last 2 date in filed DATA1 are old from the all dates...
example:
21/10/2013
21/10/2013
21/10/2013
12/11/2012
12/11/2012
12/11/2012
21/10/2013
21/10/2013
11/10/2013
11/10/2013
11/10/2013
04/10/2013
04/10/2013
in this my case, delete all records with:
04/10/2013
12/11/2012
delete all recirds based the last 2 old dates
-
- PlatinumLounger
- Posts: 4402
- Joined: 26 Apr 2010, 17:36
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: delete all recirds based the last 2 old dates
Let's say the table is named MyTable. You can use the following SQL:
DELETE DATA1 FROM MyTable WHERE DATA1 IN (SELECT TOP 2 DATA1 FROM (SELECT DISTINCT DATA1 FROM MyTable ORDER BY DATA1))
DELETE DATA1 FROM MyTable WHERE DATA1 IN (SELECT TOP 2 DATA1 FROM (SELECT DISTINCT DATA1 FROM MyTable ORDER BY DATA1))
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4402
- Joined: 26 Apr 2010, 17:36
Re: delete all recirds based the last 2 old dates
HansV wrote:Let's say the table is named MyTable. You can use the following SQL:
DELETE DATA1 FROM MyTable WHERE DATA1 IN (SELECT TOP 2 DATA1 FROM (SELECT DISTINCT DATA1 FROM MyTable ORDER BY DATA1))
tks!!!!!!!!!!!!!
But to be sure..., before to delete i need to see the result of query and make a test.
Instead to delete is possible to insert in field "test" a value "OK" on all records , for deletation.
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: delete all recirds based the last 2 old dates
To set the value of Test:
UPDATE MyTable SET Test = "OK" WHERE DATA1 IN (SELECT TOP 2 DATA1 FROM (SELECT DISTINCT DATA1 FROM MyTable ORDER BY DATA1))
You can inspect the result by looking at
SELECT * FROM MyTable WHERE Test="OK"
The delete query can then be much simpler:
DELETE Test FROM MyTable WHERE Test="OK"
UPDATE MyTable SET Test = "OK" WHERE DATA1 IN (SELECT TOP 2 DATA1 FROM (SELECT DISTINCT DATA1 FROM MyTable ORDER BY DATA1))
You can inspect the result by looking at
SELECT * FROM MyTable WHERE Test="OK"
The delete query can then be much simpler:
DELETE Test FROM MyTable WHERE Test="OK"
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4402
- Joined: 26 Apr 2010, 17:36
Re: delete all recirds based the last 2 old dates
HansV wrote:To set the value of Test:
UPDATE MyTable SET Test = "OK" WHERE DATA1 IN (SELECT TOP 2 DATA1 FROM (SELECT DISTINCT DATA1 FROM MyTable ORDER BY DATA1))
You can inspect the result by looking at
SELECT * FROM MyTable WHERE Test="OK"
The delete query can then be much simpler:
DELETE Test FROM MyTable WHERE Test="OK"
Hans sorry me but not so simple...
in effect i would to maintain always in "live" the tHree newest recordset based date in DATA1.
example:
check first if the group by get > 3 dates.
if the dates in DATA1 > 3 delete all recodset oldest.
if the the group by of DATA1 have only 3 not action.
i hope you undestand me.
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: delete all recirds based the last 2 old dates
"but not so simple..." - you are asking a different question now! Originally, you wanted to delete only the records with the two earliest dates. Now, you want to delete all records except those with the three latest dates. That is not the same!
You can run the following three queries in order:
UPDATE MyTable SET Test = "OK"
UPDATE MyTable SET Test = "No" WHERE DATA1 IN (SELECT TOP 3 DATA1 FROM (SELECT DISTINCT DATA1 FROM MyTable ORDER BY DATA1 DESC))
DELETE Test FROM MyTable WHERE Test = "OK"
You can run the following three queries in order:
UPDATE MyTable SET Test = "OK"
UPDATE MyTable SET Test = "No" WHERE DATA1 IN (SELECT TOP 3 DATA1 FROM (SELECT DISTINCT DATA1 FROM MyTable ORDER BY DATA1 DESC))
DELETE Test FROM MyTable WHERE Test = "OK"
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4402
- Joined: 26 Apr 2010, 17:36
Re: delete all recirds based the last 2 old dates
HansV wrote:"but not so simple..." - you are asking a different question now! Originally, you wanted to delete only the records with the two earliest dates. Now, you want to delete all records except those with the three latest dates. That is not the same!
You can run the following three queries in order:
UPDATE MyTable SET Test = "OK"
UPDATE MyTable SET Test = "No" WHERE DATA1 IN (SELECT TOP 3 DATA1 FROM (SELECT DISTINCT DATA1 FROM MyTable ORDER BY DATA1 DESC))
DELETE Test FROM MyTable WHERE Test = "OK"
If i'm not wrong your code maintain oly one date in "live"....
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands