SQL INSERT / SELECT, where there is no insertion table

Question

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

FROM            `tableB`

WHERE           tableA.Col1 is not already in tableB.Col1

I cannot get the WHERE clause to make sure that the record copied from table A only appears in table B ...

Answer 1

should read:

where tableA.col1 is not included (select col1 from table B)

Answer 2

You really need a SQL-2003 MERGE statement. I have included BNF for the MERGE statement (section 14.9, p837) from the standard (grotesque, but for you SQL standard) below. When translated, this may mean:

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

SQL, MERGE, , . , WHEN MATCHED, UPDATE ; IBM DB2 DELETE, 2003 ( 2008 ).

---

14.9 <merge statement> (p837)

, .

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

Answer 3

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