To update the selected columns in the table

bipinpeter · Posted: Tue Sep 10, 2013 8:08 pm

Hi All,

I want to update the data in to table TAB1 from the input file.The column details as below,

C1 --> Key field
C2
C3
C4

Input data is IN-C1, IN-C2, IN-C3, IN-C4

But I have to update the columns in the table if I have valid data in the input, otherwise it should retain the data in the table.I got two options for this.

Option 1
SELECT C2,C3,C4
INTO :HC2,:HC3,:HC4
FROM TAB1
WHERE C1 = :IN-C1

IF IN-C2 <> LOW-VALUES
MOVE IN-C2 TO :HC2.

IF IN-C3 <> LOW-VALUES
MOVE IN-C3 TO :HC3.
IF IN-C4 <> LOW-VALUES
MOVE IN-C4 TO :HC4.

UPDATE TAB1
SET C2 = :HC2
C3 = :HC3
C4 = :HC4
WHERE C1 = :IN-C1

Option 2
UPDATE TAB1
SET C2 = CASE WHEN :IN-C2 = X'00' THEN C2
ELSE :IN-C2
END
SET C3 = CASE WHEN :IN-C3 = X'00' THEN C3
ELSE :IN-C3
END
SET C4 = CASE WHEN :IN-C4 = X'00' THEN C4
ELSE :IN-C4
END
WHERE C1 = :IN-C1

I want to know that which one is best option in peformance wise.Also kindly suggest that Is any other better options availabe to achieve this requirement.

Nic Clouston · Posted: Tue Sep 10, 2013 9:19 pm

Actually you have one option:
validate input
If valid then update table
else issue error messages
get next input.

dick scherrer · Posted: Wed Sep 11, 2013 1:40 am

Hello,

If you have a high volume of data to be processed, suggest you validate the data in one process and bulk load the data into the table(s) en masse, not via individual inserts.