Home » Developer & Programmer » Forms » insert or update
insert or update [message #119583] Fri, 13 May 2005 08:14 Go to next message
jonmich005
Messages: 53
Registered: April 2005
Member
Update or insert

I have a form and here you got several rows and columns.My while works good.
If I click on update, there has to happen an update into another table that is not on my form.
BUT if There aren't data for this vehicle in this table it has to be an insert.

How can I do this????

select count(itl_id)
into :global.i
from lds_interval_tasklist
where itl_veh_id = :global.vehicle
and itl_scenario = :global.scenario
and itl_int_id = :global.t;

if to_number(:global.i) =0 then
insert into lds_interval_tasklist values(:global.vehicle,:global.scenario,:global.t,:global.itl,:lds_tl_grid.tl_grid_activity|| ' ' ||:global.aname,:lds_tl_grid.tl_grid_area_id,'T');
commit;
else
update lds_interval_tasklist
set itl_activity = :lds_tl_grid.tl_grid_activity|| ' ' ||:global.aname
where lds_interval_tasklist.itl_veh_id = :global.vehicle
and itl_id = :global.itl
and itl_int_id = :global.t;
commit;
end if;

This is what I use as code.

I hope that someone can help me by saying what is wrong.

greets
Re: insert or update [message #119626 is a reply to message #119583] Fri, 13 May 2005 09:57 Go to previous messageGo to next message
oralover
Messages: 97
Registered: January 2005
Member
first thing, always use code to format your SQL code and please also remember to mention the error you receive.

if you are using this code in post-insert or post-update of your block, it is ok and don't issue: commit.

you have used commit two times, only use after end if.

you do not have to use table name in where clause before field name when you are updating/selecting/deleting to one table.

it is good practice to use count() to check records, as it is more controlable.

Re: insert or update [message #119726 is a reply to message #119583] Sat, 14 May 2005 05:33 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
jonmich005 wrote on Fri, 13 May 2005 15:14

BUT if There aren't data for this vehicle in this table it has to be an insert.

Well, it seems like a MERGE to me. Take your documentation and look up the MERGE statement. Unfortunately, you didn't mention your version but MERGE is available as of version 9i.

It's something like this:
MERGE INTO bonuses D
   USING (SELECT employee_id, salary, department_id FROM employees
   WHERE department_id = 80) S
   ON (D.employee_id = S.employee_id)
   WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
   WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
   VALUES (S.employee_id, S.salary*0.1);


And a count() to check whether you have one record...hmm, that's quite a performance killer.

MHE
Re: insert or update [message #119730 is a reply to message #119583] Sat, 14 May 2005 06:02 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Just a side-note:
Never never use count to check whether a row is present. Use constructs like, for example, select from dual where exists
Count is way to costly to use it for checking existing rows.

hth
Previous Topic: How to Get the Count ?
Next Topic: How to create a changing field in form.....
Goto Forum:
  


Current Time: Mon Nov 04 13:44:33 CST 2024