Inserting a row not working

Question

Can someone tell me why this code isn't working? I keep getting error at line 1 and 2.

I want to insert a row into the PROBLEMTABLE table for each row in the CUSTLA table, where CustCode does not match a PK value in the CUSTCATEGORY source table.

The error at line 1 does not give me a reason but the error at line 2 says the column is ambiguously defined.

INSERT INTO PROBLEMTABLE (problemid, source_rowid, source_table, filterid, datetime, action)
    SELECT 
        PROBLEM_SEQ.NEXTVAL, ROWID, 'CUSTLA', 2, CURRENT_DATE, 'MODIFY'
    FROM 
        CUSTLA U, CUSTCATEGORY Y
    WHERE 
        U.CustCode != Y.CustCode;

SELECT * FROM PROBLEMTABLE;

Both tables CUSTLA and CUSTCATEGORY contain a column with the same name, perhaps column ROWID. In your `SELECT` statement, add table alias to the column that exists in both tables, eg `SELECT U.ROWID ...` — Serge, Oct 14 '17 at 04:50
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**25 years** ago) and its use is discouraged — marc_s, Oct 14 '17 at 06:52

Giorgos Betsos · Accepted Answer · 2017-10-14T04:58:09.937

You are not properly checking for not existing records in CUSTLAtable. Try this query instead:

INSERT INTO PROBLEMTABLE (problemid, source_rowid, source_table, 
                          filterid, datetime, action)
    SELECT PROBLEM_SEQ.NEXTVAL, ROWID, 'CUSTLA', 
           2, CURRENT_DATE, 'MODIFY'
    FROM CUSTLA U
    WHERE NOT EXISTS (SELECT 1 FROM CUSTCATEGORY Y WHERE U.CustCode = Y.CustCode)

The error you get is due to a field specified in the SELECTclause, that exists in both CUSTLA and CUSTCATEGORY tables. This is called an ambiguous field since the RDBMS engine cannot decide which one to choose from.

Note: Your query is using an implicit CROSS JOIN to check for not existing records. You are going to get a lot of redundant records this way.

Inserting a row not working

1 Answers1