MySQL Insert from another table with 2 option WHERE statement

Question

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.

Quassnoi · Accepted Answer · 2012-06-05 11:20:16Z

4

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

answered Jun 5, 2012 at 11:20

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

wildplasser · Accepted Answer · 2012-06-05 11:27:19Z

1

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

answered Jun 5, 2012 at 11:27

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

Collectives™ on Stack Overflow

MySQL Insert from another table with 2 option WHERE statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related