SQL INSERT/SELECT where not in insert table

Question

INSERT INTO     `tableA`
SELECT          `Col1`, 
                `Col2`,
                 NOW() 

FROM            `tableB`

WHERE           tableA.Col1 is not already in tableB.Col1

I can't get the WHERE clause right to ensure that the record copied from tableA only appears in table B once...

Answer 1

should read:

where tableA.col1 not in (select col1 from table B)

Answer 2

You can use the INSERT ... ON DUPLICATE KEY UPDATE syntax.

Answer 3

You really want the SQL-2003 MERGE statement. I've included the BNF for the MERGE statement (section 14.9, p837) from the standard (grotesque, but that's the SQL standard for you) below. When translated, that might translate to:

MERGE INTO TableA
    USING TableB ON TableA.Col1 = TableB.Col1
    WHEN NOT MATCHED INSERT (Col1, Col2, Col3)
                     VALUES(TableB.Col1, TableB.Col2, NOW());

I have not run that past an SQL DBMS that knows about the MERGE statement - that means there are probably bugs in it. Note that there is a WHEN MATCHED clause which can take an UPDATE in the standard; IBM DB2 also supports a DELETE clause which is not in the 2003 standard (not sure about the 2008 standard).

---

14.9 <merge statement> (p837)

Conditionally update rows of a table, or insert new rows into a table, or both.

<merge statement>    ::= 
         MERGE INTO <target table> [ [ AS ] <merge correlation name> ]
         USING <table reference> ON <search condition>
         <merge operation specification>

<merge correlation name>    ::=
         <correlation name>

<merge operation specification>    ::=
         <merge when clause> ...

<merge when clause>    ::=
         <merge when matched clause> |
         <merge when not matched clause>

<merge when matched clause>    ::=
         WHEN MATCHED THEN <merge update specification>

<merge when not matched clause>    ::= 
         WHEN NOT MATCHED THEN <merge insert specification>

<merge update specification>    ::=   UPDATE SET <set clause list>

<merge insert specification>    ::= 
         INSERT [ <left paren> <insert column list> <right paren> ]
         [ <override clause> ] VALUES <merge insert value list>

<merge insert value list>    ::= 
         <left paren> <merge insert value element>
         [ { <comma> <merge insert value element> }... ] <right paren>

<merge insert value element>    ::=
         <value expression> |
         <contextually typed value specification>