update a database table
Moderators: chulett, rschirm, roy
update a database table
seq file --> Trm ---> oracle
column1,column2,column3,column4,column5,column6,column7,column8
I have to update 4 columns( starting from column3,column4,column5,column6) in a table where column7 is not equal to 'xyz' and column3 is NULL)
AND
After for updated rows i have to set column7 to 'abc'
I am writing a coustom sql.
UPDATE Table_name
SET
column3=:3,
column4=:4,
column5=:5,
column6=:6,
column7='abc'
WHERE
column7 !='xyz' and column3 is NULL
is it the right approch.
Thanks in advance
column1,column2,column3,column4,column5,column6,column7,column8
I have to update 4 columns( starting from column3,column4,column5,column6) in a table where column7 is not equal to 'xyz' and column3 is NULL)
AND
After for updated rows i have to set column7 to 'abc'
I am writing a coustom sql.
UPDATE Table_name
SET
column3=:3,
column4=:4,
column5=:5,
column6=:6,
column7='abc'
WHERE
column7 !='xyz' and column3 is NULL
is it the right approch.
Thanks in advance
satya
UPDATE Table_name
SET
column3=:1,
column4=:2,
column5=:3,
column6=:4,
column7='abc'
WHERE
column7 !='xyz' and column3 is NULL
And also i want to pass a addition column. where i want to say
Table_name.id in ( select id from Table_name2 where Table_name2.no =:8)
i am ending up with this message
job..Oracle_OCI: ORA-00904: "addition column": invalid identifier
job..Oracle_OCI: DSP.Open GCI $DSP.Open error -100
SET
column3=:1,
column4=:2,
column5=:3,
column6=:4,
column7='abc'
WHERE
column7 !='xyz' and column3 is NULL
And also i want to pass a addition column. where i want to say
Table_name.id in ( select id from Table_name2 where Table_name2.no =:8)
i am ending up with this message
job..Oracle_OCI: ORA-00904: "addition column": invalid identifier
job..Oracle_OCI: DSP.Open GCI $DSP.Open error -100
chulett wrote:Just like you are doing is fine. Or perhaps use a job parameter if the value can change run to run. ...
satya
UPDATE Table_name
SET
column3=:1,
column4=:2,
column5=:3,
column6=:4,
column7='abc'
WHERE
column7 !='xyz' and column3 is NULL
And also i want to pass a addition column. where i want to say
Table_name.id in ( select id from Table_name2 where Table_name2.no =:8)
i am ending up with this message
job..Oracle_OCI: ORA-00904: "addition column": invalid identifier
job..Oracle_OCI: DSP.Open GCI $DSP.Open error -100
SET
column3=:1,
column4=:2,
column5=:3,
column6=:4,
column7='abc'
WHERE
column7 !='xyz' and column3 is NULL
And also i want to pass a addition column. where i want to say
Table_name.id in ( select id from Table_name2 where Table_name2.no =:8)
i am ending up with this message
job..Oracle_OCI: ORA-00904: "addition column": invalid identifier
job..Oracle_OCI: DSP.Open GCI $DSP.Open error -100
chulett wrote:Just like you are doing is fine. Or perhaps use a job parameter if the value can change run to run. ...
satya
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
Bring column1 ( i assume that this is ID) and column 8 to DB stagesatya99 wrote:UPDATE Table_name
SET
column3=:1,
column4=:2,
column5=:3,
column6=:4,
column7='abc'
WHERE
column7 !='xyz' and column3 is NULL
And also i want to pass a addition column. where i want to say
Table_name.id in ( select id from Table_name2 where Table_name2.no =:8)
i am ending up with this message
job..Oracle_OCI: ORA-00904: "addition column": invalid identifier
job..Oracle_OCI: DSP.Open GCI $DSP.Open error -100
chulett wrote:Just like you are doing is fine. Or perhaps use a job parameter if the value can change run to run. ...
Kandy
_________________
Try and Try again…You will succeed atlast!!
_________________
Try and Try again…You will succeed atlast!!
I did that, but the problem is it does not belong to this table( i guess that's the problem, How should i handle it)
UPDATE Table_name
SET
column3=:1,
column4=:2,
column5=:3,
column6=:4,
column7='abc'
column8=:8
WHERE
column7 !='xyz' and column3 is NULL
and
Table_name.id in ( select id from Table_name2 where Table_name2.no =:8)
UPDATE Table_name
SET
column3=:1,
column4=:2,
column5=:3,
column6=:4,
column7='abc'
column8=:8
WHERE
column7 !='xyz' and column3 is NULL
and
Table_name.id in ( select id from Table_name2 where Table_name2.no =:8)
kandyshandy wrote:Bring column1 ( i assume that this is ID) and column 8 to DB stagesatya99 wrote:UPDATE Table_name
SET
column3=:1,
column4=:2,
column5=:3,
column6=:4,
column7='abc'
WHERE
column7 !='xyz' and column3 is NULL
And also i want to pass a addition column. where i want to say
Table_name.id in ( select id from Table_name2 where Table_name2.no =:8)
i am ending up with this message
job..Oracle_OCI: ORA-00904: "addition column": invalid identifier
job..Oracle_OCI: DSP.Open GCI $DSP.Open error -100
chulett wrote:Just like you are doing is fine. Or perhaps use a job parameter if the value can change run to run. ...
satya
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
Ok. Instead of using a new table in the WHERE clause of this UPDATE statement, can you please have "select id from Table_name2 where Table_name2" in a look-up against a transformer before coming to target ORAOCI stage? You can join the ID from source to the ID from the lookup and use a constraint in transformer Not(IsNull(#Lookup.ID#). Please let me know if your requirement is something else.satya99 wrote:I did that, but the problem is it does not belong to this table( i guess that's the problem, How should i handle it)
UPDATE Table_name
SET
column3=:1,
column4=:2,
column5=:3,
column6=:4,
column7='abc'
column8=:8
WHERE
column7 !='xyz' and column3 is NULL
and
Table_name.id in ( select id from Table_name2 where Table_name2.no =:8)
kandyshandy wrote:Bring column1 ( i assume that this is ID) and column 8 to DB stagesatya99 wrote:UPDATE Table_name
SET
column3=:1,
column4=:2,
column5=:3,
column6=:4,
column7='abc'
WHERE
column7 !='xyz' and column3 is NULL
And also i want to pass a addition column. where i want to say
Table_name.id in ( select id from Table_name2 where Table_name2.no =:8)
i am ending up with this message
job..Oracle_OCI: ORA-00904: "addition column": invalid identifier
job..Oracle_OCI: DSP.Open GCI $DSP.Open error -100
Kandy
_________________
Try and Try again…You will succeed atlast!!
_________________
Try and Try again…You will succeed atlast!!
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
Ok. Instead of using a new table in the WHERE clause of this UPDATE statement, can you please have "select id from Table_name2 where Table_name2" in a look-up against a transformer before coming to target ORAOCI stage? You can join the ID from source to the ID from the lookup and use a constraint in transformer Not(IsNull(#Lookup.ID#). Please let me know if your requirement is something else.satya99 wrote:I did that, but the problem is it does not belong to this table( i guess that's the problem, How should i handle it)
UPDATE Table_name
SET
column3=:1,
column4=:2,
column5=:3,
column6=:4,
column7='abc'
column8=:8
WHERE
column7 !='xyz' and column3 is NULL
and
Table_name.id in ( select id from Table_name2 where Table_name2.no =:8)
kandyshandy wrote:Bring column1 ( i assume that this is ID) and column 8 to DB stagesatya99 wrote:UPDATE Table_name
SET
column3=:1,
column4=:2,
column5=:3,
column6=:4,
column7='abc'
WHERE
column7 !='xyz' and column3 is NULL
And also i want to pass a addition column. where i want to say
Table_name.id in ( select id from Table_name2 where Table_name2.no =:8)
i am ending up with this message
job..Oracle_OCI: ORA-00904: "addition column": invalid identifier
job..Oracle_OCI: DSP.Open GCI $DSP.Open error -100
Kandy
_________________
Try and Try again…You will succeed atlast!!
_________________
Try and Try again…You will succeed atlast!!
Let me back up
The problem is arising just because of
Table_name.id in ( select id from Table_name2 where Table_name2.no =:8)
1. column 8 is coming from a txt file, from which i need to to find a match against a database table.
The problem is arising just because of
Table_name.id in ( select id from Table_name2 where Table_name2.no =:8)
1. column 8 is coming from a txt file, from which i need to to find a match against a database table.
chulett wrote:What? That's an important piece of information you left out. Which "it" does not belong? First figure out what the SQL needs to look like outside of any tool before we worry about DataS ...
satya
OK. Is your job sourcing from this file? If so, then what you have should work provided the sql is syntactically correct by passing it as another field on the link. If not, explain your job design so we have some clue what you've built and where things are coming from.
Bottom line - you always need to bind every column on the tab into the sql somewhere at least once. So, if you have 8 columns defined, you must use :1 through :8 in the DML with no exceptions or gaps. If you don't need a column in the DML, do not pass it into the stage.
Bottom line - you always need to bind every column on the tab into the sql somewhere at least once. So, if you have 8 columns defined, you must use :1 through :8 in the DML with no exceptions or gaps. If you don't need a column in the DML, do not pass it into the stage.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
I am working on it will let you know
kandyshandy wrote:Ok. Instead of using a new table in the WHERE clause of this UPDATE statement, can you please have "select id from Table_name2 where Table_name2" in a look-up against a transformer before coming to target ORAOCI stage? You can join the ID from source to the ID from the lookup and use a constraint in transformer Not(IsNull(#Lookup.ID#). Please let me know if your requirement is something else.satya99 wrote:I did that, but the problem is it does not belong to this table( i guess that's the problem, How should i handle it)
UPDATE Table_name
SET
column3=:1,
column4=:2,
column5=:3,
column6=:4,
column7='abc'
column8=:8
WHERE
column7 !='xyz' and column3 is NULL
and
Table_name.id in ( select id from Table_name2 where Table_name2.no =:8)
kandyshandy wrote: Bring column1 ( i assume that this is ID) and column 8 to DB stage
satya