你不用管结果怎么得到,按照道理应该把b表中的 20 ee 和 30 ff这两条记录删除 按照2楼的理论: delete from a,a_b,b where a.aid=a_b.aid and b.bid=a_b.bid and a.属性a=cc系统提示语法错误
delete from a,a_b,b where a.aid=a_b.aid and b.bid=a_b.bid and a.属性a=bb
我也许明白了,你是这个意思?delete b from b , a , 联系集a_b属性 c where b.bID = c.bID and c.aid = a.aid and a.shuxing_a = 'bb'
delete b from a,a_b,b where a.aid=a_b.aid and b.bid=a_b.bid and a.属性a=cc
delete b from a,a_b,b where a.aid=a_b.aid and b.bid=a_b.bid and a.属性a='bb'
我测试成功:--> 生成测试数据表: [a] 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 行受影响) */如果没把问题表达清楚,一味的不对,没成功,谁也帮不了你。
create table a(aid varchar(10),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),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
--> 生成测试数据表: [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 行受影响) */???
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') 服务器: 消息 547,级别 16,状态 1,行 14 DELETE 语句与 COLUMN REFERENCE 约束 'FK__a_b__bid__70DDC3D8' 冲突。该冲突发生于数据库 'EquipmentManager',表 'a_b', column 'bid'。 语句已终止。
--1)表结构与数据--> 生成测试数据表: [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'--2)脚本与测试BEGIN TRAN --首先删除关联表 --DELETE FROM a_b --FROM a_b --INNER JOIN b --ON a_b.bID = b.bID --INNER JOIN a --ON a.aID = a_b.aID --WHERE a.shuxing_a = 'bb'--将要删除的 放在一个临时表里 SELECT * INTO #DeleteList FROM( SELECT a_b.aID,a_b.bID FROM a_b INNER JOIN b ON a_b.bID = b.bID INNER JOIN a ON a.aID = a_b.aID WHERE a.shuxing_a = 'bb')B --SELECT * FROM #DeleteList DELETE FROM a_b FROM a_b INNER JOIN #DeleteList c ON a_b.bID = c.bID DELETE FROM b FROM b INNER JOIN #DeleteList c ON b.bID = c.bID--测试删除效果 SELECT * FROM a_b SELECT * FROM b DROP TABLE #DeleteListROLLBACK TRAN
(或者由a表的某条记录通过联系集决定)
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
01 aa
02 bb
03 ccb表有两个属性: ID 属性b
10 dd
20 ee
30 ff联系集a_b属性: a表ID b表ID
01 10
02 20
02 30
怎么删除b表中的一条记录? 这条记录由a表的某条记录通过联系集决定比如 指定删除 把与a表中属性为bb这条记录有关的记录在b表中都删除!
联系集a_b属性: a表ID b表ID
01 10
02 20
02 30
按照2楼的理论:
delete from a,a_b,b
where a.aid=a_b.aid and b.bid=a_b.bid and a.属性a=cc系统提示语法错误
where a.aid=a_b.aid and b.bid=a_b.bid and a.属性a=bb
where b.bID = c.bID and c.aid = a.aid and a.shuxing_a = 'bb'
delete b from a,a_b,b
where a.aid=a_b.aid and b.bid=a_b.bid and a.属性a=cc
where a.aid=a_b.aid and b.bid=a_b.bid and a.属性a='bb'
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 行受影响)
*/如果没把问题表达清楚,一味的不对,没成功,谁也帮不了你。
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
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 行受影响)
*/???
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')
服务器: 消息 547,级别 16,状态 1,行 14
DELETE 语句与 COLUMN REFERENCE 约束 'FK__a_b__bid__70DDC3D8' 冲突。该冲突发生于数据库 'EquipmentManager',表 'a_b', column 'bid'。
语句已终止。
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'--2)脚本与测试BEGIN TRAN --首先删除关联表
--DELETE FROM a_b
--FROM a_b
--INNER JOIN b
--ON a_b.bID = b.bID
--INNER JOIN a
--ON a.aID = a_b.aID
--WHERE a.shuxing_a = 'bb'--将要删除的 放在一个临时表里
SELECT * INTO #DeleteList
FROM(
SELECT a_b.aID,a_b.bID FROM a_b
INNER JOIN b
ON a_b.bID = b.bID
INNER JOIN a
ON a.aID = a_b.aID
WHERE a.shuxing_a = 'bb')B --SELECT * FROM #DeleteList
DELETE FROM a_b
FROM a_b
INNER JOIN #DeleteList c
ON a_b.bID = c.bID DELETE FROM b
FROM b
INNER JOIN #DeleteList c
ON b.bID = c.bID--测试删除效果 SELECT * FROM a_b
SELECT * FROM b DROP TABLE #DeleteListROLLBACK TRAN