MLoad Update

Tools
Enthusiast

MLoad Update

I have to Update only one field in the table with one valuea using MultiLoad (dont want to use BTEQ since DBA dislike to update table with >100k rows with BTEQ). For instance, SQL is following...

Update DB.TABLE1
SET FIELD1=703
WHERE FIELD1=1;

Now, i have done updates usnig field input, but never with one colum value. Do i have to treate this as file input and change SQL as below or is there any other way of doign it?

Update DB.TABLE1
SET FIELD1=:FIELD1
WHERE FIELD1=1;

2 REPLIES
Enthusiast

Re: MLoad Update

Multiload will only let you update records when the Where clause contains all the primary index fields (and preferably primary key), so you will have to create a file with the primary key of every record to update.

So assuming Table1 has Fields PK1 and Pk2, do a bteq export of:

Select PK1,PK2
From DB.Table1
Where Field1 = 1
;

The you can import these records to do the update with:

Update DB.TABLE1
SET FIELD1=703
WHERE PK1 = :PK1
And PK2 = :PK2
;

Make sure there are no updates on the table between the export and import!

If the table has 10M rows, but you only want to update a few thousand, I would go back to your DBA's and argue about bteq. It is not the size of the source, it is how many rows you update which causes the overhead.

Enthusiast

Re: MLoad Update

Actually all of the records will be updated with this new value for FIELD1. Since this table is huge, I intend to use MultiLoad Update.