只有一条也删 另delete top 2 from aa where codeno='124' 或 delete top(2) from tdaichou where codeno='124' 都报错:在关键字 'top' 附近有语法错误。
请问LZ的数据库是SQL Server的吗? 什么版本?
用临时表或者CTE把“需要删除”的数据找出来,再根据这些数据delete源表
本帖最后由 DBA_Huangzj 于 2014-07-28 15:05:00 编辑
SQL Server 2000 的帮助中不就有例子? (建议把 tsqlref.chm 复制出了,查语法很方便的。)E. 在 DELETE 和 SELECT 中使用 TOP 子句 由于可以在 DELETE 语句中指定 SELECT 语句,因此还可以在 SELECT 语句中使用 TOP 子句。例如,下例从 authors 表中删除前 10 个作者。DELETE authors FROM (SELECT TOP 10 * FROM authors) AS t1 WHERE authors.au_id = t1.au_id
SQL2000的写法, set rowcount 2 delete from [表名] where [条件] set rowcount 0
看这个列子 if object_id('[tb]') is not null drop table [tb] create table [tb]([cname] varchar(4),[imoney] int) go insert [tb] select '张三',1000 union all select '李四',1 union all select '王五',1000 union all select '赵六',1 select * from [tb] delete t from (select top 2 * from [tb]) t select * from [tb] delete t from (select top 2 * from [tb]) t select * from [tb][/code]
if object_id('[tb]') is not null drop table [tb] create table [tb]([cname] varchar(4),[imoney] int) go insert [tb] select '张三',1000 union all select '李四',1 union all select '王五',1000 union all select '赵六',1 select * from [tb] delete t from (select top 2 * from [tb]) t select * from [tb]
-- 没有 2000 的环境,下面这些没用到 2005 以上的特性,你试一下 drop table x select id , colid , CAST( name as varchar(30)) name, xtype into x from syscolumns where id < 5 go select * from x order by name delete x where colid in (select top 2 colid from x where xtype ='56' order by name) select * from x order by name (14 行受影响) id colid name xtype ----------- ------ ------------------------------ ----- 3 12 bitpos 52 3 6 cid 56 3 13 colguid 165 3 14 dbfragid 56 3 3 hbcolid 56 3 8 maxinrowlen 52 3 11 nullbit 56 3 10 offset 56 3 7 ordkey 52 3 4 rcmodified 127 3 2 rscolid 56 3 1 rsid 127 3 9 status 56 3 5 ti 56(14 行受影响)(2 行受影响)id colid name xtype ----------- ------ ------------------------------ ----- 3 12 bitpos 52 3 13 colguid 165 3 3 hbcolid 56 3 8 maxinrowlen 52 3 11 nullbit 56 3 10 offset 56 3 7 ordkey 52 3 4 rcmodified 127 3 2 rscolid 56 3 1 rsid 127 3 9 status 56 3 5 ti 56(12 行受影响)
这是SQL Server 2000 的测试结果 CREATE TABLE T390845215( id int ) GO INSERT INTO T390845215 VALUES(1) INSERT INTO T390845215 VALUES(2) INSERT INTO T390845215 VALUES(3) INSERT INTO T390845215 VALUES(4) SELECT * FROM T390845215DELETE T390845215 FROM (SELECT TOP 2 * FROM T390845215) AS t1 WHERE T390845215.id = t1.idSELECT * FROM T390845215 id ----------- 1 2 3 4 id ----------- 3 4
delete top2 from源表名 where 条件【order by (看你需不需要)desc/asc】
楼主的表有主关键字吗,如果有主键,删除才好办,否则麻烦: Delete From YourTableName Where Id In (Select Top 2 Id From YourTableName Where ......)
codeno 000 111 000 111 111 333 122 122 000 你这个数据没有唯一性。2000里面 你要么加个自增列吧 Alter table tb add id int identity(1,1) 然后 Delete From YourTableName Where Id In (Select Top 2 Id From YourTableName Where ......)
不行呀,派生表 't' 不可更新,因为其定义中包含 TOP 子句。 2000可以用 set rowcount 2 delete [tb] set rowcount 0
delete top(2)
from [表名]
where [条件]
另delete top 2 from aa where codeno='124'
或
delete top(2) from tdaichou where codeno='124'
都报错:在关键字 'top' 附近有语法错误。
(建议把 tsqlref.chm 复制出了,查语法很方便的。)E. 在 DELETE 和 SELECT 中使用 TOP 子句
由于可以在 DELETE 语句中指定 SELECT 语句,因此还可以在 SELECT 语句中使用 TOP 子句。例如,下例从 authors 表中删除前 10 个作者。DELETE authors
FROM (SELECT TOP 10 * FROM authors) AS t1
WHERE authors.au_id = t1.au_id
delete from [表名] where [条件]
set rowcount 0
if object_id('[tb]') is not null drop table [tb]
create table [tb]([cname] varchar(4),[imoney] int)
go
insert [tb]
select '张三',1000 union all
select '李四',1 union all
select '王五',1000 union all
select '赵六',1
select * from [tb]
delete t from (select top 2 * from [tb]) t
select * from [tb]
delete t from (select top 2 * from [tb]) t
select * from [tb][/code]
create table [tb]([cname] varchar(4),[imoney] int)
go
insert [tb]
select '张三',1000 union all
select '李四',1 union all
select '王五',1000 union all
select '赵六',1
select * from [tb]
delete t from (select top 2 * from [tb]) t
select * from [tb]
codeno
000
111
000
111
111
333
122
122
000
....
-- 没有 2000 的环境,下面这些没用到 2005 以上的特性,你试一下
drop table x
select id , colid , CAST( name as varchar(30)) name, xtype into x
from syscolumns where id < 5
go
select * from x order by name
delete x where colid in (select top 2 colid from x where xtype ='56' order by name)
select * from x order by name
(14 行受影响)
id colid name xtype
----------- ------ ------------------------------ -----
3 12 bitpos 52
3 6 cid 56
3 13 colguid 165
3 14 dbfragid 56
3 3 hbcolid 56
3 8 maxinrowlen 52
3 11 nullbit 56
3 10 offset 56
3 7 ordkey 52
3 4 rcmodified 127
3 2 rscolid 56
3 1 rsid 127
3 9 status 56
3 5 ti 56(14 行受影响)(2 行受影响)id colid name xtype
----------- ------ ------------------------------ -----
3 12 bitpos 52
3 13 colguid 165
3 3 hbcolid 56
3 8 maxinrowlen 52
3 11 nullbit 56
3 10 offset 56
3 7 ordkey 52
3 4 rcmodified 127
3 2 rscolid 56
3 1 rsid 127
3 9 status 56
3 5 ti 56(12 行受影响)
CREATE TABLE T390845215(
id int
)
GO
INSERT INTO T390845215 VALUES(1)
INSERT INTO T390845215 VALUES(2)
INSERT INTO T390845215 VALUES(3)
INSERT INTO T390845215 VALUES(4)
SELECT * FROM T390845215DELETE T390845215
FROM (SELECT TOP 2 * FROM T390845215) AS t1
WHERE T390845215.id = t1.idSELECT * FROM T390845215
id
-----------
1
2
3
4 id
-----------
3
4
Delete From YourTableName Where Id In (Select Top 2 Id From YourTableName Where ......)
000
111
000
111
111
333
122
122
000
你这个数据没有唯一性。2000里面
你要么加个自增列吧 Alter table tb add id int identity(1,1) 然后
Delete From YourTableName Where Id In (Select Top 2 Id From YourTableName Where ......)
2000可以用
set rowcount 2
delete [tb]
set rowcount 0