请问如何写一个sql，只保留表的前500条记录，其余的都删除

[qq_pride]

如题

[xjqqxjqq]

同意sbaz(万神渡劫) 的
因为rownum是只出现在查询结果中的一个伪序号，所以只能用rownum<n或者rownum<=n；

[youngqp]

楼上正解

[zzwind5]

create table a as
select * from table_name where rownum<=500;

truncate table table_name;

insert into table_name
select * from a;

drop table a;

[超叔csdn]

delete from tablename
where tablename.rowid not in(
select rowid from tablename
where rownum<501)

[qq_pride]

请问为什么不直接delete from table_name where rownum>500呢

[aimcy]

delete from tablename where rowid not in
(select top 500 rowid from tablename)

[hlp912]

楼上的兄弟，楼主问的是oracle中怎么处理，不是问的sql server中怎么处理。
在oracle中：

delete from tablename
where tablename.rowid not in(
select rowid from tablename
where rownum<=500)

在sql server中
delete from tablename where rowid not in
(select top 500 rowid from tablename)

[highphg]

SQL Server有rowid吗？？？？？

[hevin]

应该也有类似的东西，只是sql server没有提供方法取出来。