Home » SQL & PL/SQL » SQL & PL/SQL » could u plz clear my doubts???
icon11.gif  could u plz clear my doubts??? [message #236493] Thu, 10 May 2007 01:58 Go to next message
rajgeo
Messages: 1
Registered: May 2007
Location: Mumbai
Junior Member

Dear Friends,

Can any one give me a solution to my doubts?
My doubts are 1. can we add unique constraint to a table which already have duplicate rows. Is it any way that we can add unique constraint to a table which has duplicate rows? ( I don't want to delete duplicate rows from table).

2. I have a table which consist of 20 records.I want to delete 19 records out of 20. Can any one tell how to delete 19 rows from table without using where condition?


Regards,

Rajesh
Re: could u plz clear my doubts??? [message #236504 is a reply to message #236493] Thu, 10 May 2007 02:14 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
1. Look in the manual for
   ALTER TABLE ... ADD CONSTRAINT ... NOVALIDATE


2. No.

HTH.
Michael
Re: could u plz clear my doubts??? [message #236506 is a reply to message #236493] Thu, 10 May 2007 02:19 Go to previous messageGo to next message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Hello.

Point 1 has been answered, but point 2? Why on earth would you want to do this? Which 19 rows out of the 20? A random 19? It's a strange requirement, surely you need to delete a specific 19? If not, then maybe you need to log a TAR with Oracle to get an enhancement for 11g, DELETE FROM table LEAVING 1 RANDOM ROW;

Wink

Regards

[Updated on: Thu, 10 May 2007 02:20]

Report message to a moderator

Re: could u plz clear my doubts??? [message #236507 is a reply to message #236506] Thu, 10 May 2007 02:23 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Deleting any subset of a table without using the WHERE keyword is not practical, but it might make for a passable lunchtime conversation topic if you had nothing to talk about that was actually interesting.

@mchadder raises an interesting proposition. I wonder if the SAMPLE clause could be used somehow. I'm just not curious enough to waste time trying it.

Ross Leishman
Re: could u plz clear my doubts??? [message #236510 is a reply to message #236493] Thu, 10 May 2007 02:34 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
system@ORCL-SQL>>create table s( x int);

Table created.

system@ORCL-SQL>>insert into s values(100);

1 row created.

system@ORCL-SQL>>insert into s select * from s;

1 row created.

system@ORCL-SQL>>select * from s;

         X
----------
       100
       100

system@ORCL-SQL>>alter table s 
add constraint uq unique(x) deferrable
 initially deferred
  novalidate;

Table altered.


As for your second question, I cannot understand why would you want to delete from a table without using where clause?
Re: could u plz clear my doubts??? [message #236519 is a reply to message #236506] Thu, 10 May 2007 02:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Silly thing but:
declare
  i pls_integer := 0;
begin
  for rec in (select rowid rid from mytable) loop
    i := i + 1;
    exit when i=20;
    delete mytable where rowid = rec.rid;
  end loop;
end;
/

Regards
Michel
Re: could u plz clear my doubts??? [message #236528 is a reply to message #236519] Thu, 10 May 2007 03:02 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
./fa/2431/0/
Oops.

Ross Leishman
  • Attachment: whatthe.JPG
    (Size: 64.83KB, Downloaded 406 times)
Re: could u plz clear my doubts??? [message #236563 is a reply to message #236493] Thu, 10 May 2007 04:46 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Just for 'fun'

create table t20 as 
     select rownum rn, object_name 
     from user_objects 
     where rownum <21;


create table ct20 as 
       select rn , max(object_name) object_name  
       from t20 
       group by rn;

drop table t20;

alter table ct20 rename to t20;


What a bizarre request. Methinks there is more to the question than meets the eye.
Re: could u plz clear my doubts??? [message #236570 is a reply to message #236528] Thu, 10 May 2007 04:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@Ross, Woops! Laughing

@pablolee, don't think there is more than a silly thing. Nice trick.

Regards
Michel

Re: could u plz clear my doubts??? [message #236579 is a reply to message #236563] Thu, 10 May 2007 05:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
pablolee wrote on Thu, 10 May 2007 11:46
Just for 'fun'

create table t20 as 
     select rownum rn, object_name 
     from user_objects 
     where rownum <21;


create table ct20 as 
       select rn , max(object_name) object_name  
       from t20 
       group by rn;

drop table t20;

alter table ct20 rename to t20;


What a bizarre request. Methinks there is more to the question than meets the eye.


Sorry:
SQL> create table t20 as
  2       select rownum rn, object_name
  3       from user_objects
  4       where rownum <21;

Table created.

SQL> create table ct20 as
  2         select rn , max(object_name) object_nam
  3         from t20
  4         group by rn;

Table created.

SQL> select * from t20
  2  /

        RN OBJECT_NAME
---------- ----------------------------------------
         1 I_1_AC_S1
         2 I_2_AC_S1
         3 JOB_ID_SEQ
         4 JOB_INSTANCE_ID_SEQ
         5 JOB_INSTANCE_IO_ID_SEQ
         6 JOB_TASK_CONFIG_ID_SEQ
         7 JOB_TYPE_ID_SEQ
         8 JOB_TYPE_IO_FLOW_ID_SEQ
         9 JOB_TYPE_TASK_ID_SEQ
        10 JT_ATTR_ID_SEQ
        11 MESSAGE_HANDLER_SEQ
        12 MESSAGE_SEQ
        13 OSI_ACCEPTANCE_ANSWER_SEQ
        14 OSI_BANK_DETAILS_SEQ
        15 OSI_COLLECTIVE_CONTRACT_SEQ
        16 OSI_COLLECTIVE_CONTR_RULES_SEQ
        17 OSI_PACKAGE_GROUP_SEQ
        18 OSI_PACKAGE_PRODUCT_SEQ
        19 OSI_POLICY_MEMBER_SEQ
        20 OSI_POLICY_SEQ

20 rows selected.

SQL> select * from ct20;

        RN OBJECT_NAME
---------- ----------------------------------------
         1 I_1_AC_S1
         6 JOB_TASK_CONFIG_ID_SEQ
        11 MESSAGE_HANDLER_SEQ
        13 OSI_ACCEPTANCE_ANSWER_SEQ
         2 I_2_AC_S1
        14 OSI_BANK_DETAILS_SEQ
        20 OSI_POLICY_SEQ
         4 JOB_INSTANCE_ID_SEQ
         5 JOB_INSTANCE_IO_ID_SEQ
         8 JOB_TYPE_IO_FLOW_ID_SEQ
        17 OSI_PACKAGE_GROUP_SEQ
         3 JOB_ID_SEQ
         7 JOB_TYPE_ID_SEQ
        18 OSI_PACKAGE_PRODUCT_SEQ
         9 JOB_TYPE_TASK_ID_SEQ
        10 JT_ATTR_ID_SEQ
        12 MESSAGE_SEQ
        15 OSI_COLLECTIVE_CONTRACT_SEQ
        16 OSI_COLLECTIVE_CONTR_RULES_SEQ
        19 OSI_POLICY_MEMBER_SEQ

20 rows selected.


But, your final comment makes up for this Smile
Re: could u plz clear my doubts??? [message #236602 is a reply to message #236579] Thu, 10 May 2007 06:18 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Gaaaaaa what a plonker Smile

I meant:

drop table t20;
create table t20 as select rownum rn, object_name from user_objects where rownum <21;
create table ct20 as select rn , max(object_name) object_name  from t20  having rn = 1 group by rn;
drop table t20;
alter table ct20 rename to t20;

Sorry 'bout that
Re: could u plz clear my doubts??? [message #236608 is a reply to message #236602] Thu, 10 May 2007 06:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Cheater! ./fa/1579/0/
"having" is "where" nothing else. Razz

Regards
Michel
Re: could u plz clear my doubts??? [message #236613 is a reply to message #236608] Thu, 10 May 2007 06:51 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Laughing
Re: could u plz clear my doubts??? [message #236715 is a reply to message #236613] Thu, 10 May 2007 12:34 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
...

[Updated on: Thu, 10 May 2007 12:44]

Report message to a moderator

Re: could u plz clear my doubts??? [message #236717 is a reply to message #236715] Thu, 10 May 2007 12:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://en.wikipedia.org/wiki/Plonker

Too late, skooman, I saw your post. Smile

[Updated on: Thu, 10 May 2007 12:45]

Report message to a moderator

Re: could u plz clear my doubts??? [message #237932 is a reply to message #236602] Wed, 16 May 2007 02:52 Go to previous messageGo to next message
Alien
Messages: 245
Registered: June 1999
Senior Member
To continue on pablolee's trick:

create table ct20 as 
select a.* from t20 a
join (select max(rn) rnm from t20) b
on a.rn=b.rnm


A join condition is not a where for sure, right Razz
Re: could u plz clear my doubts??? [message #237994 is a reply to message #237932] Wed, 16 May 2007 05:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Nice trick.
You can do it with Analytics too:
drop table t20;

create table t20 as select rownum rn, object_name from user_objects where rownum <21;

create table ct20 as select distinct first_value(rn) over (order by rn desc) rn
                                    ,first_value(object_name) over (order by rn desc) object_name 
                                     from t20;
                                    
drop table t20;

alter table ct20 rename to t20;
Re: could u plz clear my doubts??? [message #238115 is a reply to message #237994] Wed, 16 May 2007 14:09 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Michel, I found the same solution myself! (just in the spirit of this forum). But thanks for the help anyway, LOL.
Previous Topic: Get records with positive values within 10y
Next Topic: interpreting fields..
Goto Forum:
  


Current Time: Thu Dec 08 12:44:24 CST 2016

Total time taken to generate the page: 0.11359 seconds