CODEWITHSUNDEEP
sqlsql-insert
Mike Dark
Mike Dark

Reputation: 1

Inserting a row not working

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;

Upvotes: 0

Views: 67

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

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.

Upvotes: 1

Related Questions