CODEWITHSUNDEEP
mysqlsql
Phil
Phil

Reputation: 14671

MySQL Insert from another table with 2 option WHERE statement

I have done my research but can not figure out how to do this. It is super simple to insert from another table but I want to include WHERE statements.

I want to insert value of a single column, column_Q from table A into table B's column_Q WHERE table A's column_W = '100' and column_Q does not already exist in table B.

I tried:

INSERT INTO B (column_Q) select DISTINCT(column_Q) 
from A WHERE column_W = 100 AND b.column_Q<>a.column_Q;

Where am I doing wrong?

PS. Both tables already contain values. No field is Null.

Upvotes: 1

Views: 185

Answers (2)

wildplasser
wildplasser

Reputation: 44250

You cannot refer to the left side of the "assignment", because there is no current row from B to compare to (that would be the one you are inserting) You need to check if a similar row is already present in B, like in:

INSERT INTO B (column_Q)
SELECT DISTINCT(A.column_Q) 
FROM A 
WHERE A.column_W = 100
AND NOT EXISTS (
    SELECT *
    FROM B
    WHERE B.column_Q = A.column_Q
    );

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425471

INSERT
INTO    b (q)
SELECT  DISTINCT q
FROM    a
WHERE   a.w = 100
        AND a.q NOT IN
        (
        SELECT  q
        FROM    b
        )

If your b.q has a UNIQUE constraint defined on it, then just use:

INSERT
IGNORE
INTO    b (q)
SELECT  q
FROM    a
WHERE   w = 100

Upvotes: 4

Related Questions