Update SQL Query
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 232
- Joined: Fri Sep 30, 2005 4:52 am
- Contact:
Update SQL Query
Target table consists of
ENAME DEPT
Diana 10
Steve 20
Kane 20
Smith 10
Diana 20
For above if i want update employee "Diana" deparment 10 to 30 where i will get only ename and dept information from source
say UPDATE EMPLOYEE SET DEPT=30 WHERE ENAME LIKE 'Diana' AND DEPT = 10
How can we achive above in datastage ?
Thanks,
Anupam
ENAME DEPT
Diana 10
Steve 20
Kane 20
Smith 10
Diana 20
For above if i want update employee "Diana" deparment 10 to 30 where i will get only ename and dept information from source
say UPDATE EMPLOYEE SET DEPT=30 WHERE ENAME LIKE 'Diana' AND DEPT = 10
How can we achive above in datastage ?
Thanks,
Anupam
Re: Update SQL Query
A 'like' with no wildcards is the same as saying 'equals', which turns this into a plain old ordinary update statement.sb_akarmarkar wrote:say UPDATE EMPLOYEE SET DEPT=30 WHERE ENAME LIKE 'Diana' AND DEPT = 10
If you really mean to use the 'like' you'll need custom sql and to wrap your field value in the appropriate wildcard characters for your unmentioned database - '%' for Oracle, for example.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 232
- Joined: Fri Sep 30, 2005 4:52 am
- Contact:
Use an OCI/ODBC stage to select from the target table rows of interest, transform then however you desire, then pass the result to an OCI/ODBC to load. E T L
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Not much clear with your requirement.
What is that you expect to change in on DETP derivation.
What is that you expect to change in
Code: Select all
If DEPT=30 And ENAME = 'Diana' Then 10 Else DEPT.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
-
- Participant
- Posts: 232
- Joined: Fri Sep 30, 2005 4:52 am
- Contact:
I want to update DEPT to 10 where DEPT is 30kumar_s wrote:Not much clear with your requirement.
What is that you expect to change inon DETP derivation. ...Code: Select all
If DEPT=30 And ENAME = 'Diana' Then 10 Else DEPT.
In database data in is already exists.... I want to update table with gerated query or userdefined query in target databas.e
In tarnsformer map DEPT---->DEPT
how to pass value to WHERE clause and SET clause
Thanks,
Anupam
-
- Participant
- Posts: 232
- Joined: Fri Sep 30, 2005 4:52 am
- Contact:
one more new table consists of employee information where dept is 30 for employee shifted for one department to other...DSguru2B wrote:Need more info on how did you get the number 30. Are you aggregating the dept grouped by ENAME and then want to go into the table and update all dept keyed on ENAME?
Need more clarification on your requirement.
Thanks,
Anupam
If the question is simply how do I use a column twice in the generated sql - once in the where clause and once not - you need User Defined SQL for that. Simple example:
Pass the column in twice (different names, of course) once with the old and once with the new value. Make the one to be used in the where clause as the key and then make sure you handle your parameter markers properly, either numbered appropriately or in the correct order.
Code: Select all
UPDATE TABLE X SET Y=A WHERE Y=B
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 232
- Joined: Fri Sep 30, 2005 4:52 am
- Contact:
Yes craig got me correctly.....chulett wrote:If the question is simply how do I use a column twice in the generated sql - once in the where clause and once not - you need User Defined SQL for that. Simple example:
Pass the column in twice (different names, of course) once with the old and once with the new value. Make the one to be used in the where clause as the key and then make sure you handle your parameter markers properly, either numbered appropriately or in the correct order.Code: Select all
UPDATE TABLE X SET Y=A WHERE Y=B
I cant use parameter as there can be department 20 change to 30 also
any suggestions...
Thanks,
Anupam
-
- Participant
- Posts: 232
- Joined: Fri Sep 30, 2005 4:52 am
- Contact: