insert or update [message #119583] |
Fri, 13 May 2005 08:14 |
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 |
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 #119730 is a reply to message #119583] |
Sat, 14 May 2005 06:02 |
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
|
|
|