代码之家 › 专栏 › 技术社区 › alionthego

如果不存在,则在尝试独立于另一个表的条件插入时不起作用

not-exists sql mysql

alionthego · 技术社区 · 6 年前

我要实现的是,仅当用户在独立表中不阻止当前用户时,才允许将用户添加到组中:

INSERT INTO users_to_groups(user_id, group_id) 
VALUES (1,'A') 
WHERE NOT EXISTS (SELECT * FROM users_to_users WHERE user_id_a=2 AND user_id_b=1 AND user_blocked=1);

我试图一次插入多行

2 回复 | 直到 6 年前

Gordon Linoff 6 年前

INSERT INTO users_to_groups(user_id, group_id) 
    SELECT x.user_id, x.group_id
    FROM (SELECT 1 as user_id, 'A' as group_id) x
    WHERE NOT EXISTS (SELECT 1
                      FROM users_to_users utu
                      WHERE utu.user_id_a = 2 AND
                            utu.user_id_b = 1 AND
                            utu.user_blocked = 1
                     );

Joe Taras 6 年前

INSERT

INSERT INTO users_to_groups(user_id, group_id) 
SELECT 1,'A'
FROM dual
WHERE NOT EXISTS
    (SELECT * FROM users_to_users
     WHERE user_id_a=2 AND user_id_b=1 AND 
     user_blocked=1);

SELECT

INSERT INTO users_to_groups(user_id, group_id) 
SELECT u.id,'A'
FROM users u
WHERE NOT EXISTS
    (SELECT * FROM users_to_users
     WHERE user_id_a=u.id AND user_id_b=1 AND 
     user_blocked=1);