Pl sql inserting updating
This would reduce the number of executions needed by the If statement. Begin Insert Into table Values(); Exception When Dup_Value_On_Index then Update Table Set = Where; End; HIHCOULD YOU SHOW ME A REAL EXAMPLE? Begin Insert Into table2 Values(select mydate, amount from my table1); Exception When Dup_Value_On_Index then Update table2 Set amount = table1.amount amount Where mydate = table1.mydate ; End; Begin Insert Into table2 Select date, amount From table1 Where date = something; Commit; Exception -- Insert failed, so update record When Dup_Value_On_Index Then Begin Update table2 Set amount = amount -- add amount from sub query (Select t.amount From table1 t Where = something) Where date = something; Commit; End; -- end exception End; -- end programupdate table B set (amount) = (select amount from table A where table A.mydate = table B.mydate) where mydate in (select mydate from table A); This still would not take my current value in TABLEB and increment it.. TABLEA mydate amount -------- --------- 3/10 200 TABLEB mydate amount -------- --------- 3/10 1000 after my update.. TABLEB mydate amount -------- -------- 3/10 1200 My primary key being "mydate" Thanks! Oracle Database automatically executes a trigger when a specified event takes place, which may be in the form of a system event or a DML statement being issued against the table.Triggers can be: Trigger names must be unique with respect to other triggers in the same schema.
1) Create the 'product' table and 'product_price_history' table Once the above update query is executed, the trigger fires and updates the 'product_price_history' table.
TABLEA and TABLEB consists of a DATE_T column which has a date and AMOUNT column, which has totals.
I'd like for the script to read TABLEA and either insert or update TABLEB based on date.
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.