updating in oracle table which doesn't contain any key cols

[kirankota79]

I have transformed only 3 columns out of 10 columns in a table and i want to update only those three columns in the table. I don't have any key columns in the table. I there a way that i can do this without messing other columns. currently i am doing truncate and load the whole table since i don't have any key columns.

[dnsjain]

no. Key columns are only the way to update the rows. You do not need to define those key columns in the Oracle database, they are just for DataStage for updating the data. You can come up with some logical key columns to update the data.

[chulett]

Don't confuse 'key columns' with indexes, they are not equivalent. A table doesn't need a primary key in order to be updated via sql, nor does it need one for DataStage. The 'key' column attribute just marks which column(s) should be used in the 'where' clause of the update DML.

[chulett]

Is there a way that i can do this without messing other columns.

How would you do this through Toad / SQL Plus and 'normal' sql? That's the same way you do it with DataStage.

[kirankota79]

to be honest i haven't done this before. It would be helpful if you give me some idea. Do i need to write a procedure and use stored procedure stage?

Thanks

[kirankota79]

to be honest i haven't done this before. It would be helpful if you give me some idea. Do i need to write a procedure and use stored procedure stage?

Thanks

[chulett]

You haven't done what before, updated a record using sql?

This is simple sql we're talking about here so no need for a procedure. Something else to understand is if you don't have indexes over the field(s) in your where clause, each update will be a full table scan and thus not all that speedy. However, that's just a normal sql thing.

To update three out of ten columns in a table, only mention those three non-key columns in the output link. Plus, of course, you'll need whatever fields are needed to identify which record to update - those would be your key field. For example, field A is the key field and B,C,D are the fields that need to be updated, thus non-key. Assume they are declared in the stage in the order shown. The stage would generate the sql you need, something on the order of:

Code: Select all

UPDATE YOUR_TABLE SET B = :2, C = :3, D = :4 WHERE A = :1

The numbers are positional parameter markers, bind variables that get the values from every row sent to the stage.

[ray.wurlod]

You still have to call it Key in DataStage - that's what gets its value to replace the parameter marker in the WHERE clause.

[kirankota79]

Since ROWID of a table is always unique to that table, I am also selecting it in the input oracle stage and using it as primary key when updating the table. Is it a good idea?

[chulett]

That should be fine as long as you handle it correctly.

[SettValleyConsulting]

This will probably work in this scenario, but using ROWID may fail in some circumstances.

Oracle ROWID is a psuedo-column, determined by the RDBMS rather than the data. It is unique within a table and is fixed in most but not all circumstances. For example exporting and reimporting a table will change ROWIDs, also I believe ROWIDs on Partitioned and Index-organised tables may change.

If none of these is an issue then you can use ROWID, however you really should have a unique identifier for each row within the data content itself, even if it is a surrogate key. If there really is not such a column(s) then I would query the database design.

Phil Clarke.

[kirankota79]

Thanks for your reply....we can check for the duplicte rowids before updating and if there are no duplicates then we can go ahead. Is that OK?

and i think i dont have all the privilages to query the databse design! Only privileges that i have are in the list that we have in the parallel job developers guide for accessing oracle.

[SettValleyConsulting]

When I said 'query' the db design I was using the word in the sense of to question or challenge the design of the table.

It is one of the fundamental principles of relational table design that there should be an attribute in the table that uniquely identifies a row.

http://en.wikipedia.org/wiki/Unique_key

If there is not such a key in the application data (a natural key), the database designer should have added a surrogate key. Personally, if there is not such a key I would want an explanation from the database designer as to the reason why not.

[SettValleyConsulting]

Thanks for your reply....we can check for the duplicte rowids before updating and if there are no duplicates then we can go ahead. Is that OK?

You don't need to check for duplicates, ROWIDs will always be unique within a table. You only need to be certain the ROWID will not change between reading and updating the row. It sounds like this is the case in your situation.

In general, I would still prefer a 'proper' primary key, though.

[chulett]

After all this, don't fall back on the belief that you *need* a primary key to update a record. That seemed to be the original confusion.