I am having a single target table which needs to be updated from 7 different tables which are joined and having aggrgated for a fact columns.
The single target table is having a PI which is auto incremented by 1.
In order to update the rows, I am placing a multiple columns(composite key) in where clause in order to update the columns which uniquely identifies the rows. Here I am not using PI column because it is not related to other table keys. Since the dataset is coming from 7 tables and loading into 1 table.
But on running the mload utility, It is given an error "Multiload update statement is invalid".
On searching in net, I found that there should be a PI for the table.
My doubt is
1. Is it mandatory that the table should contain the PI and use that PI for updating in where clause?
2. Can't We use a set of columns in where clause which uniquely identifies the entire row?
3. Can we place the same column/s in set and where clause of update stmt.
Thanks & Regards
When you read the MLoad manual you'll notice that the table's PI (plus partition) must be referenced in the WHERE-clause and must not be updated.
And again, what you need is a SQL Update but no MLoad.