MLoad Update

Tools

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
N/A

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.

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.