CODEWITHSUNDEEP
mysqlsqldatabasesql-insertwhere-clause
alionthego
alionthego

Reputation: 9743

INSERT sql statement based on condition from another table

I am trying to insert values into a table based on conditions from another table. Ideally I would want something like this:

INSERT INTO table_a SET user_id = 'contactId'
WHERE
    table_b.user_id = 'userA' 
    AND table_b.contact_id = 'contactId' 
    AND user_blocked = false

So before creating the new table_a entry, I want to make sure the user is not blocked by checking table_b.

I know I can't user INSERT with WHERE so not sure how I can do this.

Upvotes: 0

Views: 966

Answers (3)

Lironsh
Lironsh

Reputation: 11

You can use T-SQL for this, or build a stored procedure.

First check if the user_id is blocked in table_b and if the answer is false insert the value to table_a

This is an example:

DECLARE @user_id INT = 1234

IF ((SELECT user_blocked FROM table_b b WHERE b.USER_id = @user_id) = 'false')
BEGIN
    INSERT INTO table_a 
    VALUES(@user_id,....)
END

Upvotes: 0

CodeMan
CodeMan

Reputation: 706

You can use "Insert From Select" . For example this link will help you.

Upvotes: 0

GMB
GMB

Reputation: 222462

Seems like you are looking for an INSERT ... SELECT query :

INSERT INTO table_a (user_id) 
SELECT 'contactId'
FROM table_b AS tb
WHERE
    tb.user_id = 'userA' 
    AND tb.contact_id = 'contactId' 
    AND tb.user_blocked = false

If the subquery does not return anything, the outer INSERT will not happen.

Upvotes: 4

Related Questions