Re: update/insert in oracle

In article <u3Sl5.792$Xg.6879_at_news-server.bigpond.net.au>,   "Cobra__77" <Cobra__77_at_hotmail.com> wrote:
> I have the following query
>
> insert into mytab(x,y,val) select x,y,sum(val) from otherTab group by
 x,y
>
> where x and y is my primary keys (ie compsite key)
>
> It works fine sometimes...but now it crashes...because I may already
 have
> some rows in mytab with x and y....ie I get primary key
 violations.....and
> the insert fails.....

Maybe you should consider some alternatives. It appears that the table mytab is essentially replaced. You might try:

1. truncating the table each time you run it
2. dropping the table and using a creat table mytab as select... syntax
3. use a view
4. use a snapshot with regular updating

--
Jim Lyons                     |  Network Systems Analyst
Entertech - The IC2 Institute |  512-482-0273, ext. 269
University of Texas at Austin |  jlyons_at_weblyons.com
http://www.weblyons.com


Sent via Deja.com http://www.deja.com/
Before you buy.