如何检查多个表中的记录是否存在
问题描述:
如何在将记录插入表之前检查三个不同表中是否存在bomItem
字段值。我要检查如何检查多个表中的记录是否存在
1. `bomitem` of `BOMHEAD` Table with `itemId` of `Table A`
or
2. `bomitem` of `BOMHEAD` Table with `itemId` of `Table B`
or
3. `bomitem` of `BOMHEAD` Table with `itemId` of `Table C`
是否相等
如果存在无论是在三代表其良好的BOMHEAD
表中创建一个记录。上述三张表中唯一的共同字段是itemId
字段,它们包含广泛不同的属性。
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BOMHEAD](
[bomItem] [int] NOT NULL,
[bomRev] [nvarchar](6) NOT NULL,
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
样本数据
Table A
100199
100200
100201
Table B.
200199
200200
200201
200202
table C
400199
400200
400201
现在,我想补充我要录制BOMHEAD
INSERT INTO BOMHEAD (bomItem) VALUES (400199); //OK since exist in Table C
INSERT INTO BOMHEAD (bomItem) VALUES (200202); //OK since exist in Table B
INSERT INTO BOMHEAD (bomItem) VALUES (500202); //NO because doesnt exist in either A or B or C tables
答
使用EXISTS
检查记录是否存在的表格中。这里有一种方法
INSERT INTO BOMHEAD(bomItem)
SELECT bomItem
FROM (VALUES (400199),
(200202),
(200202))tc(bomItem)
WHERE EXISTS (SELECT 1 FROM TableA A WHERE tc.bomItem = A.itemId)
OR EXISTS (SELECT 1 FROM TableB B WHERE tc.bomItem = B.itemId)
OR EXISTS (SELECT 1 FROM TableC C WHERE tc.bomItem = C.itemId)
另一种方法(可能不如上面的查询效率高。与你的真实数据运行它来检查的性能)。
INSERT INTO BOMHEAD
(bomItem)
SELECT bomItem
FROM (VALUES (400199),
(200202),
(200202))tc(bomItem)
WHERE EXISTS (SELECT 1
FROM (SELECT itemId FROM TableA A
UNION ALL
SELECT itemId FROM TableB B
UNION ALL
SELECT itemId FROM TableC C) a
WHERE a.itemId = tc.bomItem)
什么是来源?从哪张表中拉出记录并插入“BOMHEAD”中。 –
表A或B或C.为了将记录插入到'BOMHEAD'表中,必须有表A或表B或表C(仅相对于BomItem字段)中的匹配记录。 – afri
当所有三个表都有'bomitem' –