UPDATE SQL USING INNER JOIN

[cnoel]

I have an update job that matches new values in a remote database, and I am having difficulties with getting the updates written back to the original table.

Table 1 has two fields that I am concerned with

DCN (Not Unique)
PolicyNumber (Not Unique)

I am getting a feed (Comma delimited file) from a remote platform containing the DCN and updated PolicyNumber:

DCN, POLICYNUMBER
'ABCDEFGH','1234567890'

I load these values into Table 2. These values represent more than one table, so there will be DCN's in the file that aren't located on TABLE1.

The problem comes from the attempted update. I have tried two methods; an inner join update, and a subselect update.

The INNER JOIN Update is written as

UPDATE TABLE1 INNER JOIN TABLE2 ON TABLE1.DCN = TABLE2.DCN SET TABLE1.POLICYNUMBER = TABLE2.POLICYNUMBER

This query fails due to syntax.

The subselect query is written as

UPDATE TABLE1 SET DCN = (SELECT TABLE2.POLICYNUMBER WHERE TABLE1.DCN=TABLE2.DCN)

This also fails with the following error:

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=2, TABLEID=28, COLNO=1" is not allowed. SQLSTATE=23502

One thing I did try was to use MS ACCESS w/ ODBC to DB2, and the inner join works on that platform. However, this needs to be scheduled job in DB2, so this approach is not desireable.

I also looked into an INSERT-UPDATE import possiblity, but this hasn't gotten very far.

Any help would be appreciated.

Thanks
Curtis

 

[MarcLodge]

Hi Curtis,
I think you've got to look at what you are trying to achieve as it seems a little confused at the moment.

The UPDATE subselect appears to be trying to update the DCN value with that exists in the POLICYNUMBER field for the DCN in question!
Does the table have any further fields, particularly key fields? You have stated that the DCN and POLICYNUMBER are non unique, and from your SQL it looks as if you wish to update ALL data for a particular DCN/POLICYNUMBER which I suspect is not correct.

I think that the way you probably want to go is via the UPDATE without the INNER JOIN, but need a little more info. Get back to us with a little more info on what the process is doing, and what you are trying to achieve.

Marc

 

[cnoel]

OK, lets see if a cup of coffee helps at all.

I have a table (Table1) that contains the following fields.

ITEMID (index)
DCN (string)
DocumentType (string)
PolicyNumber (string)
ReceiptDate (date)
ScanDate (date)


We are pulling information from another system where the policy number is assigned to the item, using the DCN as an identifier. So nightly, I export all DCN's requiring updates to a file and another group sends me a file back with the DCN, PolicyNumber match in a delimited text file. I import the contents of this file to a temp table (Table2).

DCN (string)
PolicyNumber(string)

I need to update the PolicyNumbers in Table1 using the values listed in Table2 - matching on the DCN. There is a possibility that the DCN exists more than once on either table.

As mentioned above, I have attempted an inner join, a subselect (mistyped above: should have been SET PolicyNumber =), and an INSERT_UPDATE to no avail. I know there is a simple method, but I think I have been looking at this a little too long, and have tunnel vision.

Thanks

 

[MarcLodge]

Hi Curtis,
OK, see what you're up to now. The easiest way to do what you are doing is with the statement:

UPDATE TABLE1 T1
SET POLICYNUMBER =
(SELECT T2.POLICYNUMBER
FROM TABLE2 T2
WHERE T2.DCN = T1.DCN)

I think that the problem is that you don't appear to have uniqueness in your inner select. From what you have said (unless I have misunderstood which is quite possible) the inner select can produce more than one row.

I'm not sure what happens in this instance but it is obviously quite strange. From your error message it almost appears to be that DB2 is attempting to insert a row to the table that just contains a POLICYNUMBER, and because of the NULL constraints, gets severely annoyed!

I would suggest that you need to make the data in the Table2 unique by dropping duplicates. Presumably only one Policynumber can be correct.

Hope this helps.

Marc