设a表有两个属性:aID shuxing_a(属性a)
01 aa
02 bb
03 ccb表有两个属性: bID shuxing_b(属性b)
10 dd
20 ee
30 ff联系集a_b属性: aID bID
01 10
02 20
02 30
怎么删除b表中的一条记录? 这条记录由a表的某条记录通过联系集决定比如 指定删除 把与a表中属性为bb这条记录有关的记录在b表中都删除!即应该把b表中的 20 ee 和 30 ff这两条记录删除
(本人认为还应该把联系集中的记录 02 20和02 30这两条记录也一并删除)要求用SQL语句回帖。
01 aa
02 bb
03 ccb表有两个属性: bID shuxing_b(属性b)
10 dd
20 ee
30 ff联系集a_b属性: aID bID
01 10
02 20
02 30
怎么删除b表中的一条记录? 这条记录由a表的某条记录通过联系集决定比如 指定删除 把与a表中属性为bb这条记录有关的记录在b表中都删除!即应该把b表中的 20 ee 和 30 ff这两条记录删除
(本人认为还应该把联系集中的记录 02 20和02 30这两条记录也一并删除)要求用SQL语句回帖。
01 10
02 20
02 30
是另外一个表,还是你的结果?
where b.bID = c.bID and c.aid = a.aid and a.shuxing_a = 'bb'
where b.bID = c.bID and b.bID in(20,30)delete b from b , a_b c
where b.bID = c.bID and b.bID in(20,30)
where b.bID = c.bID and b.bID in(20,30)delete b where bID in(20,30)修改,分两步删除
IF OBJECT_ID('[a]') IS NOT NULL
DROP TABLE [a]
GO
CREATE TABLE [a] ([aID] [nvarchar](10),[shuxing_a] [nvarchar](10))
INSERT INTO [a]
SELECT '01','aa' UNION ALL
SELECT '02','bb' UNION ALL
SELECT '03','cc'--> 生成测试数据表: [b]
IF OBJECT_ID('[b]') IS NOT NULL
DROP TABLE [b]
GO
CREATE TABLE [b] ([bID] [int],[shuxing_b] [nvarchar](10))
INSERT INTO [b]
SELECT '10','dd' UNION ALL
SELECT '20','ee' UNION ALL
SELECT '30','ff'--> 生成测试数据表: [a_b]
IF OBJECT_ID('[a_b]') IS NOT NULL
DROP TABLE [a_b]
GO
CREATE TABLE [a_b] ([aID] [nvarchar](10),[bID] [int])
INSERT INTO [a_b]
SELECT '01','10' UNION ALL
SELECT '02','20' UNION ALL
SELECT '02','30'--SELECT * FROM [a]
--SELECT * FROM [b]
--SELECT * FROM [a_b]-->SQL查询如下:
DELETE c
FROM b, a_b c
WHERE b.bID = c.bID
AND b.bID IN (20, 30)DELETE b
WHERE bID IN (20, 30)SELECT * FROM [b]
SELECT * FROM [a_b]
/*
bID shuxing_b
----------- ----------
10 dd(1 行受影响)aID bID
---------- -----------
01 10(1 行受影响)
*/如果没把问题表达清楚,一味的不对,没成功,谁也帮不了你。
foreign key aID references a表
foreign key bID references b表
--> 生成测试数据表: [a]
IF OBJECT_ID('[a]') IS NOT NULL
DROP TABLE [a]
GO
CREATE TABLE [a] ([aID] [nvarchar](10) PRIMARY KEY,[shuxing_a] [nvarchar](10))
INSERT INTO [a]
SELECT '01','aa' UNION ALL
SELECT '02','bb' UNION ALL
SELECT '03','cc'--> 生成测试数据表: [b]
IF OBJECT_ID('[b]') IS NOT NULL
DROP TABLE [b]
GO
CREATE TABLE [b] ([bID] [int] PRIMARY KEY,[shuxing_b] [nvarchar](10))
INSERT INTO [b]
SELECT '10','dd' UNION ALL
SELECT '20','ee' UNION ALL
SELECT '30','ff'--> 生成测试数据表: [a_b]
IF OBJECT_ID('[a_b]') IS NOT NULL
DROP TABLE [a_b]
GO
CREATE TABLE [a_b] ([aID] [nvarchar](10) REFERENCES a(aID),[bID] [int] REFERENCES b(bID))
INSERT INTO [a_b]
SELECT '01','10' UNION ALL
SELECT '02','20' UNION ALL
SELECT '02','30'--SELECT * FROM [a]
--SELECT * FROM [b]
--SELECT * FROM [a_b]-->SQL查询如下:
DELETE c
FROM b, a_b c
WHERE b.bID = c.bID
AND b.bID IN (20, 30)DELETE b
WHERE bID IN (20, 30)SELECT * FROM [b]
SELECT * FROM [a_b]
/*
bID shuxing_b
----------- ----------
10 dd(1 行受影响)aID bID
---------- -----------
01 10(1 行受影响)
*/???
insert into a values('01', 'aa')
insert into a values('02', 'bb')
insert into a values('03', 'cc')
create table b(bid varchar(10),shuxing_b varchar(10))
insert into b values('10', 'dd')
insert into b values('20', 'ee')
insert into b values('30', 'ff')
create table a_b(aid varchar(10), bid varchar(10))
insert into a_b values('01', '10')
insert into a_b values('02', '20')
insert into a_b values('02', '30')
godelete b from b , a , a_b c
where b.bid = c.bid and c.aid = a.aid and a.shuxing_a = 'bb'select * from b
/*
bid shuxing_b
---------- ----------
10 dd(所影响的行数为 1 行)
*/drop table a , b, a_b我测试也成功.楼主你太..............
create table a(aid varchar(10) primary key,shuxing_a varchar(10))
insert into a values('01', 'aa')
insert into a values('02', 'bb')
insert into a values('03', 'cc')
create table b(bid varchar(10) primary key,shuxing_b varchar(10))
insert into b values('10', 'dd')
insert into b values('20', 'ee')
insert into b values('30', 'ff')
create table a_b(aid varchar(10)references a(aid), bid varchar(10)references b(bid))
insert into a_b values('01', '10')
insert into a_b values('02', '20')
insert into a_b values('02', '30')delete b from b , a , a_b c
where b.bid = c.bid and c.aid = a.aid and a.shuxing_a = 'bb'服务器: 消息 547,级别 16,状态 1,行 14
DELETE 语句与 COLUMN REFERENCE 约束 'FK__a_b__bid__70DDC3D8' 冲突。该冲突发生于数据库 'EquipmentManager',表 'a_b', column 'bid'。
语句已终止。