Home » SQL & PL/SQL » SQL & PL/SQL » making the table distinct ! (10g , XP SP2)
icon5.gif  making the table distinct ! [message #315215] Sun, 20 April 2008 01:46 Go to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

now I have that table :-

create table t1 ( id number , N varchar2(11));

insert into t1 values (1,'as');
insert into t1 values (1,'as');
insert into t1 values (1,'as');
insert into t1 values (1,'as');
insert into t1 values (2,'db');
insert into t1 values (2,'db');
insert into t1 values (2,'db');
insert into t1 values (2,'db');

commit;


and when we retrieve the values , it will be like that :-

SQL> select * from t1;

        ID N
---------- -----------
         1 as
         1 as
         1 as
         1 as
         2 db
         2 db
         2 db
         2 db

8 rows selected.


now I want make same table with distinct values , but I failed :-

SQL> update t1
  2  set id = ( select distinct id from t1 ) ,
  3  n = ( select distinct n from t1 );
set id = ( select distinct id from t1 ) ,
           *
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row 


in the end I made another table with distinct values from first one ..... like that :-

SQL> create table t2 as select distinct  id ,  n  from t1 ;

Table created.

SQL> select * from t2;

        ID N
---------- -----------
         1 as
         2 db


but I'm still wondering if there any way to
make the same table ( t1 ) updated to their distinct values ... like in table t2
Rolling Eyes Question Exclamation
Re: making the table distinct ! [message #315218 is a reply to message #315215] Sun, 20 April 2008 02:01 Go to previous message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You just want to delete duplicates.
It is a FAQ.

Regards
Michel
Previous Topic: DBMS_MVIEW.REFRESH_ALL_MVIEWS
Next Topic: utl_file error
Goto Forum:
  


Current Time: Tue Dec 06 08:23:19 CST 2016

Total time taken to generate the page: 0.09193 seconds