Update Oracle table with Rownum

[Anonymous]

How to update oracle table with rownum. I am rownum as key to update a record.  But i am getting below error.

Error message: ORA-00904: "rownum": invalid identifier.

[nivedhitha]

@senthilswing ,

can you post screenshots of your job?

[Anonymous]

This the select query in tOracleinput

select rownum,col1 from table

Updating col1 with some value by using rownum as key in tOracleoutput. I am getting error.

[Anonymous]

rownum is a pseudocolumn which indicates the row number in oracle. I am using rownum as a key to update a column since my table doesn't have any unique value.

Is it possible to use rownum in tOracleoutput. But we can update the logic in Sql developer editor.

[Ganshyam]

Hello,

We can create sequence using tmap and update the records using this sequence. As the rownum will generate the unique id against the record fetched by the SQL query.

Hope this solves your problem.

Regards

Ganshyam Patel

[rmartin2 · Accepted solution]

As said before, "rownum" is not a sequence generated. It's a variable created "on the spot" to guide you to locate your data (kind of).

You can use this variable to generate a sequence of your own, but it will reflect the SELECT statement.

So if you sort in different manner it will not match.

As suggested, if you have no way of having a primary key, you'll have to manage a sequence. You can index it, and it's then very easy to manage, update or insert thanks to triggers !

Just put a trigger "BEFORE INSERT" (or AFTER) and add the id from the nextvalue of the sequence !