Delete statement

[marcela24]

I want to have in my table just the 150 newest records and delete all the rest. But my table is very big and ill declare a row count to do it in steps or it will fail. Does anybody know how can i do that?

 

[dickiebird]

How do you identify the newest - is there a datetime column ?
Post the table ddl, please

Dickie Bird (-)))

 

[marcela24]

tpoints ( id, date, lat, long )
that´s the table.

 

[marcela24]

there's a big problem!!!
tpoints ( id, lat, long, id_auto )
tauto ( id, name )

I want to stay with the 150 newest records from each auto. Sorry.

 

[pascalsql]

Although not very nice, that could be the fastest:
1 Transfer the top 150 in a temp table
2 delete or truncate your table
3 copy back

-- Copy
select top 150 *
into #tmp_tpoints
from tpoints
order by date desc

select A.* 
into #tmp_tauto
from tauto A
join #tmp_tpoints P on P.id = A.id  --assuming it's the join

-- Carefuly check you get what you want
select * from #tmp_tpoints
select * from #tmp_tauto

-- Empty the tables
truncate table tpoints
truncate table tauto

-- Copy back
insert into tpoints select * from #tmp_tpoints
insert into tauto select * from #tmp_tauto

-- Clean up
drop table #tmp_tpoints
drop table #tmp_tauto

You may want to wrap your code in a transaction. If so use delete instead of truncate

nota: I can't test this code, no sql server at home :-(