Re: select/insert/delete
- From: Michael Moore <michaeljmoore@xxxxxxxxx>
- To: rafu@xxxxxx
- Date: Wed, 8 Jun 2011 09:24:17 -0700
Hi Timo,
I'm pretty much locked into the two table design. There are existing tables
with a lot of code that depends on them being just the way they are. I can't
add columns, views, partitions etc. These are not options for me.
Thanks and regards,
Mike
On Wed, Jun 8, 2011 at 2:26 AM, Timo Raitalaakso <rafu@xxxxxx> wrote:
> 8.6.2011 2:12, Michael Moore wote:
>
> Is there a single SQL statement that can:
>>
>
> You need a single statement that handles select delete and insert. How
> about using a single update statement to do the job?
> Relationally talking a view is a table. So your tables might lool like
> something like this.
>
> create table c(n number primary key, deleted timestamp);
>
> create view a as select n from c where deleted is null;
>
> create view b as select n from c where deleted is not null;
>
>
> insert into a (n) values (1);
>
> insert into a (n) values (2);
>
>
> update c set deleted=systimestamp where n=1;
>
>
>
>
> If you need to physically separate those a and b rows you could use
> partitioning. A partition physcally thinking is actually a table.
>
>
> create table cp(n number primary key, deleted timestamp) partition by
> list(deleted) (partition a values (null),partition b values (default))
> enable row movement;
>
> create or replace view a as select n from cp where deleted is null;
>
> create or replace view b as select n from cp where deleted is not null;
>
> insert into a (n) values (1);
>
> insert into a (n) values (2);
>
>
> update cp set deleted=systimestamp where n=1;
>
>
>
> So the insert and delete parts of your request are handled. You did not
> mention why you need the select part.
> Because it is first in your list one might think that your prosess first
> takes a look if there is somtehing to do and after that do stuff.
> It is most often enough just do stuff using the where part of update
> clause. Or do you need some results back?
> If so take a look returning_clause of update.
> http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_10008.htm#i2126358
>
>
> drop view a;
>
> drop view b;
>
> drop table c;
>
> drop table cp;
>
>
> --
> Timo Raitalaakso
> http://rafudb.blogspot.com
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
