Luckily enough, we can fake a staging table using DUAL: create table employees ( employee_id integer not null, employee_name varchar2(100) not null); alter table employees add constraint employee_pk primary key (employee_id); create or replace procedure merge_employee( i_emp_id in integer, i_emp_name in varchar2 ) is begin merge into employees e using (select i_emp_id id, i_emp_name name from dual) s on (e.employee_id = s.id) when matched then update set employee_name = when not matched then insert (employee_id, employee_name) values (s.id, s.name); end; / With the merge statement, we now have a single more complex query instead of the 3 we started with, or the two of the refined approach.

merge into MY_TABLE tgt using (select [expressions] from dual ) src on (src.key_condition = tgt.key_condition) when matched then update tgt set tgt.column1 = src.column1 [,...] when not matched then insert into tgt ([list of columns]) values (src.column1 [,...]); Unfortunately many frameworks such as Hibernate treat all database errors (e.g.

JEThis works perfect, but what if I have multiple days in table A.. mydate amount -------- --------- 3/10 100 3/10 200 3/11 100 If I use this update method, it gives an error that it returns more than a single row..

pl sql if inserting updating-89

I want to have a pl/sql in Oracle 8i to do and insert or update.

I'd like it read information from TABLEA and insert or update TABLEB.

Nidhi" id="ctl00_m_m_i_ctl00_gr_ctl25_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="1455371" / You can try something like this, depending upon the value of a column, you can either call any function, procedure which return value to the trigger...

create or replace trigger TRBIU_Test Employees before insert or update on Employees referencing OLD as old NEW as new for each row begin :new.email := Case when :new.employee_id = 1000 then func1(1000) when :new.employee_id = 1002 then [email protected]' End; If :new.employee_id = 1001 then PROC2(:new.employee_id,:new.email); end if; end; where PROC2 is something like this CREATE OR REPLACE PROCEDURE PROC2 ( Emp_Id_IN IN VARCHAR2 , Email_OUT OUT VARCHAR2 ) AS BEGIN If Emp_Id_IN = 1001 then Email_OUT := [email protected]'; end if; END PROC2; Hope this would help to resolve the issue....

Often this problem is solved with a select statement and then an IF statement, eg: declare v_exists varchar2(1) := ' F'; begin begin select ' T' into v_exists from employee where employee_id := i_employee_id; exception when no_data_found then null; end; if v_exists = ' T' then update employee set ... When coding a procedure, you should try and get an idea of how the procedure will be used.

where employee_id = i_employee_id else insert ( ... In this case, the question to ask whether the procedure will mostly be used to update existing employee records, or insert new ones.

Nidhi create or replace trigger Trigmytest1 before insert on mytest1 referencing OLD as old NEW as new for each row begin if :new.col1 = 100 then" id="ctl00_m_m_i_ctl00_gr_ctl17_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="1455205" / In that case won't it become mutating trigger because i will be updating in the trigger which will aging fire the trigger and so on.

Whole point of having Automonus transactions will fail. statements in your procedures but if you just pass values back, you are setting values for extra columns in the row which you already updating/inserting, so trigger is not fired again thx" id="ctl00_m_m_i_ctl00_gr_ctl23_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="1455236" / it will be mutating trigger if you have insert/update statements in your procedures but if you just pass values back, you are setting values for extra columns in the row which you already updating/inserting, so trigger is not fired again thx create or replace trigger TRBIU_Test Employees before insert or update on Employees referencing OLD as old NEW as new for each row begin :new.email := Case when :new.employee_id = 1000 then func1(1000) when :new.employee_id = 1002 then [email protected]' End; If :new.employee_id = 1001 then PROC2(:new.employee_id,:new.email); end if; end; where PROC2 is something like this CREATE OR REPLACE PROCEDURE PROC2 ( Emp_Id_IN IN VARCHAR2 , Email_OUT OUT VARCHAR2 ) AS BEGIN If Emp_Id_IN = 1001 then Email_OUT := [email protected]'; end if; END PROC2; Hope this would help to resolve the issue....

So if the same date exists in TABLEB, i'd like for it to take teh current value in AMOUNT and add it to the existing value. Thanks for any guidance and if there's a quicker method to do this besides using a PL/SQL, that would be great too. how can I insert into a table with multiple primary keys... insert into table B select trunc(mydate), myname, sum(amount) from table A where trunc(mydate) not in (select trunc(mydate) from table B ) group by trunc(mydate), myname; commit; In the " where trunc(mydate) not in (select....)" I want to add another field... "where trunc(mydate), myname not in (select..." Thanks!