keep old values in columns when updating Oracle table?

sattibabu_2004 · Post by **sattibabu_2004** » Thu Oct 12, 2006 1:27 pm

I am using Server jobs. I have to read XML file with 15 tags as input to update exisitng Oracle table with 15 columns. All XML tags are optional except ID(primary key) column. So, Each XML message is not neccesserily containing all values in all messages. If mesage 1 comes with column 4 with null value and other fileds with valid values, I have to update with new values in table with values from XML except for column 4. I am not sure till message comes to processing which field is coming as null. In summary I can say that, if one field is null in input I want ot keep the exisitng value for the corresponding column in table's row, but update other columns for that row. I have to use only one job. How can I solve my problem? I can't use PX in this case for other reasons. Thank you for your help.

dsdev750 · Post by **dsdev750** » Thu Oct 12, 2006 1:41 pm

You could have a user-defined update statement like:

UPDATE tbl
SET col1 = NVL(:col1, col1),
col2=NVL(:col2, col2)..
WHERE pk_col=:pk_col

ray.wurlod · Post by **ray.wurlod** » Thu Oct 12, 2006 2:08 pm

Welcome aboard. :D

Perform a lookup against the target table, or a hashed file containing relevant rows and columns from the target table. This will allow you to determine the current values, which you can use in the UPDATE statement if the source value is null.

sattibabu_2004 · Post by **sattibabu_2004** » Thu Oct 12, 2006 3:29 pm

ray.wurlod wrote:Welcome aboard. :D

Perform a lookup against the target table, or a hashed file containing relevant rows and columns from the target table. This will allow you to determine the current values, wh ...

It worked. Thank you all for your help.