CODEWITHSUNDEEP
mysqlsqlnot-exists
alionthego
alionthego

Reputation: 9703

WHERE NOT EXISTS not working when trying to insert based independent on condition from another table

I'm trying to insert values into a table based on a condition from another table. Not based on ignoring duplicate values. But I keep getting a syntax error. Not sure how to proceed.

What I want to achieve is only allowing a user to be added to a group if in an independent table that user is not blocking the current user:

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);

EDIT

I am trying to insert multiple rows at a time

Upvotes: 0

Views: 198

Answers (2)

Joe Taras
Joe Taras

Reputation: 15379

If you want to INSERT only one row you can try as follow:

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);

If you want to INSERT a real query result you can change the SELECT statement with your logic

For example:

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);

But you can't use INSERT INTO ... VALUES with WHERE condition because you have a syntax error

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You can do:

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
                     );

Upvotes: 2

Related Questions