Greetings,
Need some help to implement the following logic, if possible, in a
single sql
statement.

Requirement:

(1) I have two tables say table1 and table2
(2) I have to iterate through table1 records and need to check some
conditions against table2 records
(3) When the conditions are NOT MATCHING I want to insert the same
record(under consideration) of table1 into table2
(4) If conditions are MATCHING then I need to update some columns of
table2 and insert the same record
from table1 to table 2 (just like point 3).

My approach was to do it using MERGE statement but I dont know how to
update plus insert
the records (i.e point 4) in the WHEN MATCHED clause of merge
statement.

Can someone give me some hint or some workaround?
If possible, I want to avoid using cursors.

I hope I was able to make myself clear

Any help would be appreciated.

DB version information:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production

TIA

On Apr 23, 10:44 pm, "pankaj_wolfhun...@yahoo.co.in"
<pankaj_wolfhun...@yahoo.co.in> wrote:
> Greetings,
> Need some help to implement the following logic, if possible, in a
> single sql
> statement.
>
> Requirement:
>
> (1) I have two tables say table1 and table2
> (2) I have to iterate through table1 records and need to check some
> conditions against table2 records
> (3) When the conditions are NOT MATCHING I want to insert the same
> record(under consideration) of table1 into table2
> (4) If conditions are MATCHING then I need to update some columns of
> table2 and insert the same record
> from table1 to table 2 (just like point 3).
>
> My approach was to do it using MERGE statement but I dont know how to
> update plus insert
> the records (i.e point 4) in the WHEN MATCHED clause of merge
> statement.
>
> Can someone give me some hint or some workaround?
> If possible, I want to avoid using cursors.
>
> I hope I was able to make myself clear
>
> Any help would be appreciated.
>
> DB version information:
> Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
> PL/SQL Release 10.2.0.3.0 - Production
>
> TIA

One more way i just tried.

As inserting of records (point 3) is a common activity, I first
inserted the records in table2 using table1
and then updated table2 using MERGE based on the business conditions.
It seem to be working fine.

But still something like a single sql statement or something else?

On Apr 23, 3:31 pm, "pankaj_wolfhun...@yahoo.co.in"
<pankaj_wolfhun...@yahoo.co.in> wrote:
> On Apr 23, 10:44 pm, "pankaj_wolfhun...@yahoo.co.in"
>
>
>
>
>
> <pankaj_wolfhun...@yahoo.co.in> wrote:
> > Greetings,
> > Need some help to implement the following logic, if possible, in a
> > single sql
> > statement.
>
> > Requirement:
>
> > (1) I have two tables say table1 and table2
> > (2) I have to iterate through table1 records and need to check some
> > conditions against table2 records
> > (3) When the conditions are NOT MATCHING I want to insert the same
> > record(under consideration) of table1 into table2
> > (4) If conditions are MATCHING then I need to update some columns of
> > table2 and insert the same record
> > from table1 to table 2 (just like point 3).
>
> > My approach was to do it using MERGE statement but I dont know how to
> > update plus insert
> > the records (i.e point 4) in the WHEN MATCHED clause of merge
> > statement.
>
> > Can someone give me some hint or some workaround?
> > If possible, I want to avoid using cursors.
>
> > I hope I was able to make myself clear
>
> > Any help would be appreciated.
>
> > DB version information:
> > Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
> > PL/SQL Release 10.2.0.3.0 - Production
>
> > TIA
>
> One more way i just tried.
>
> As inserting of records (point 3) is a common activity, I first
> inserted the records in table2 using table1
> and then updated table2 using MERGE based on the business conditions.
> It seem to be working fine.
>
> But still something like a single sql statement or something else?

Why the need for ONE SQL statement?
Two statements for a single transaction as easily as one.
Not, this is not just you. A few other posts here and other groups
have asked for similar one step solutions. I just don't understand the
why of it. Maybe there is a patch of obfuscated C programmers learning
SQL?? 8^)

Ed