1.查询 select * from [Table] where name<>'AA' 2.删除 delete from [Table] where name='AA' select * from [Table]
SELECT name , age INTO # FROM TB WHERE NAME<>'AA' GO TRUNCATE TABLE TB INSERT TB SELECT * FROM #
create table #RR ( id int identity(1,1) primary key not null, UserName nvarchar(20), Age int ) insert into #RR select 'AA',11 union all select 'BB',20 union all select 'AA',30 union all select 'CC',40 union all select 'DD',15 delete from #RR where id in (select id from #RR where UserName='AA')
如果楼主要重新排ID,对删除后的数据使用:select id = (select count(id) from tb where id < t.id) + 1,name,age from tb t
Delete from tb where name='AA'; select id=(select count(1) from tb where id<t.id)+1,name,age from tb t;
如果id具有identity属性,则自动会重新分配ID。
select * from [Table] where name<>'AA'
declare @TabA table ( id int identity(1,1) primary key not null, name varchar(20), age int ) insert @TabA select 'AA',11 union all select 'BB',20 union all select 'AA',30 union all select 'CC',40 union all select 'DD',15 union all select 'BB',18declare @TabB table ( id int identity(1,1) primary key not null, name nvarchar(20), age int ) insert into @TabB select name,age from @TabA where name<>'AA'select * from @TabB/* 查询结果: id name age 1 BB 20 2 CC 40 3 DD 15 4 BB 18 */
declare @TabA table ( id int identity(1,1) primary key not null, name varchar(20), age int ) insert @TabA select 'AA',11 union all select 'BB',20 union all select 'AA',30 union all select 'CC',40 union all select 'DD',15 union all select 'BB',18select id=identity(int,1,1),name,age into TabB from @TabA where name<>'AA'select * from TabBdrop table TabB
2.删除 delete from [Table] where name='AA'
select * from [Table]
GO
TRUNCATE TABLE TB
INSERT TB SELECT * FROM #
(
id int identity(1,1) primary key not null,
UserName nvarchar(20),
Age int
)
insert into #RR select 'AA',11
union all select 'BB',20
union all select 'AA',30
union all select 'CC',40
union all select 'DD',15
delete from #RR where id in (select id from #RR where UserName='AA')
select id=(select count(1) from tb where id<t.id)+1,name,age from tb t;
declare @TabA table
(
id int identity(1,1) primary key not null,
name varchar(20),
age int
)
insert @TabA select 'AA',11
union all select 'BB',20
union all select 'AA',30
union all select 'CC',40
union all select 'DD',15
union all select 'BB',18declare @TabB table
(
id int identity(1,1) primary key not null,
name nvarchar(20),
age int
)
insert into @TabB select name,age from @TabA where name<>'AA'select * from @TabB/*
查询结果:
id name age
1 BB 20
2 CC 40
3 DD 15
4 BB 18
*/
declare @TabA table
(
id int identity(1,1) primary key not null,
name varchar(20),
age int
)
insert @TabA select 'AA',11
union all select 'BB',20
union all select 'AA',30
union all select 'CC',40
union all select 'DD',15
union all select 'BB',18select id=identity(int,1,1),name,age into TabB from @TabA where name<>'AA'select * from TabBdrop table TabB