表结构如下-----------------------------------
user 用户表id username
1 aaaaaa
2 bbbbbb
3 cccccc
-------------------------------------
userinfo 用户信息表userid sex
1 男
2 女
3 男现在我要实现的目的是 删除user 表里面姓别为男的记录
查询的时候容易查,但是删除的时候不知道怎么操作了,
注:只删除用户表[user]里面的记录,不要删除userinfo里面的记录,!
user 用户表id username
1 aaaaaa
2 bbbbbb
3 cccccc
-------------------------------------
userinfo 用户信息表userid sex
1 男
2 女
3 男现在我要实现的目的是 删除user 表里面姓别为男的记录
查询的时候容易查,但是删除的时候不知道怎么操作了,
注:只删除用户表[user]里面的记录,不要删除userinfo里面的记录,!
t
from
[user] t
where
exists(select 1 from userinfo b where userid=t.id and sex='男')
delete a from [user] a,userinfo b where a.id=b.userid and b.sex='男'
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-18 17:05:37
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[user]
if object_id('[user]') is not null drop table [user]
go
create table [user]([id] int,[username] varchar(6))
insert [user]
select 1,'aaaaaa' union all
select 2,'bbbbbb' union all
select 3,'cccccc'
--> 测试数据:[userinfo]
if object_id('[userinfo]') is not null drop table [userinfo]
go
create table [userinfo]([userid] int,[sex] varchar(2))
insert [userinfo]
select 1,'男' union all
select 2,'女' union all
select 3,'男'
--------------开始查询--------------------------
delete
t
from
[user] t
where
exists(select 1 from userinfo b where userid=t.id and sex='男')
select * from [user]
----------------结果----------------------------
/* id username
----------- --------
2 bbbbbb(1 行受影响)
*/
insert into [user] values(1 , 'aaaaaa')
insert into [user] values(2 , 'bbbbbb')
insert into [user] values(3 , 'cccccc')
create table userinfo(userid int, sex varchar(10))
insert into userinfo values(1 , '男')
insert into userinfo values(2 , '女')
insert into userinfo values(3 , '男')
godelete [user] from [user] m where m.id in (select userid from userinfo where sex = '男')select * from [user]drop table [user],userinfo/*
id username
----------- ----------
2 bbbbbb(所影响的行数为 1 行)
*/
delete from user a where exists(select null from userinfo b where a.id=b.userid and sex='男'