Hi guys

hi,
Is it possible to write a update and insert in a single query, i.e., what we do in update strategy transformation. if yes pls give on example or query?

Yes we can write update and insert in a single query like

[B]insert into t1(a, b, c)
select d, e, f from t2
on duplicate key update b = e, c = f;[/B]
Use MySQL’s non-standard ON DUPLICATE KEY UPDATE extension to accomplish the insert and update in a single step. This is the fastest method.

There are other ways to write this statement, for example using the VALUES function, which can help simplify complex queries by referring to the value which would have been inserted into the given column:

[B]insert into t1(a, b, c)
select d, e, f from t2
on duplicate key update b = values(b), c = values(c);[/B]
The disadvantage to this approach is lack of portability, of course. Inserting and updating in a single statement is highly non-standard.

Hi venkatesh,

There is one function called Merge using that you can conditionally insert else update the records. If the record exist it will update else it will insert the record. I hope your requirement is this only.
Below is the syntax for the same.

[B]merge into
table_dest d
using
table_source s
on
(s.id = d.id)
when matched then update set d.txt = s.txt
when not matched then insert (id, txt) values (s.id, s.txt);[/B]

Thanks
Uday

[quote=udkumar;22016]Hi venkatesh,

There is one function called Merge using that you can conditionally insert else update the records. If the record exist it will update else it will insert the record. I hope your requirement is this only.
Below is the syntax for the same.

[B]merge into [/B]
[B]table_dest d[/B]
[B]using[/B]
[B]table_source s[/B]
[B]on [/B]
[B](s.id = d.id)[/B]
[B]when matched then update set d.txt = s.txt[/B]
[B]when not matched then insert (id, txt) values (s.id, s.txt);[/B]

Thanks
Uday[/quote]

[B]@Uday[/B],

Is the same possible in Oracle 9.2 Version..!
________________________________________________________
And in addition to the above, we can use conditional statements also.