问题描述
假设有两个表 - UserRole (UserRoleID, UserID, RoleID) 和 UserPermission (UserPermissionID, UserID, PermissionID, RegionID).
Assume two tables - UserRole (UserRoleID, UserID, RoleID) and UserPermission (UserPermissionID, UserID, PermissionID, RegionID).
我有以下带有子查询的 INSERT 以在 UserPermission 中为每个具有特定 RoleID 的 UserID 创建新行:
I have the following INSERT w/subquery to create new rows in UserPermission for every UserID with a certain RoleID:
INSERT INTO UserPermission (UserPermissionID, UserID, PermissionID, RegionID)
SELECT NEWID(), UserID, @PermID, NULL
FROM UserRole WHERE RoleID = '<specific guid>'
如何检查我要插入的行是否在数据库中不存在?我知道如何检查单个记录(如果不存在(等等))，但是检查特定 UserID 和 PermissionID 的组合是否存在的最佳方法是什么(总是@PermID - 一个特定的 GUID)存在在 UserPermission 表中的一行中?
How can I check to see whether the rows I'm inserting don't already exist in the database? I know how to check for an individual record (IF NOT EXISTS(blah blah)), but what would be the best way to check whether a combination of a particular UserID and PermissionID (always going to be @PermID - a particular GUID) exist in a row in the UserPermission table?
推荐答案
应该可以这样实现:
INSERT INTO UserPermission (UserPermissionID, UserID, PermissionID, RegionID)
SELECT NEWID(), UserID, @PermID, NULL
FROM UserRole r
WHERE RoleID = '<specific guid>'
AND NOT EXISTS (SELECT * FROM UserPermission WHERE UserID = r.UserID AND @PERMID = PermissionID);
这篇关于SQL 插入到 w/子查询 - 检查是否不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！