SIMILAR TUPLES

oracle India
  • 13 years ago

    hello... i have an doubt...can anyone rectify it?

    in a table, i m having rollno,name,gender,mark1,mark2. in this two rows have the same value(rollno also same). i want to delete a single row..how can i do it. if i use simple delete with any condition, both the row will be deleted.... :-( help me plz...thanks in advance

  • 13 years ago

    Hi,

     

    That’s why primary keys are need in a table to distinct rows. In this case you can use two conditions: DELETE FROM some_table WHERE rollno=1 AND name=’name’;

  • 13 years ago
    thanks for u r reply.... but i didnt set any primary key and it has same roll number,name etc etc.... help me..
  • 13 years ago

    Hi,

     

    You have two rows with the same data (duplicated rows) or just records with the same rollno?

     

    What you want to do, change the rollno, delete the duplicated records; what.

     

    Give the schema of the table.

  • 13 years ago
    hey lexian...sorry man. here we should not alter any information.but we have to delete it.this question was arised by my professor.so there may be appropriate answer.can anyone help me?
  • 13 years ago
    y YAAR...no reply for my thread??? dont the answer for anyone? Hmm [^o)]
  • 13 years ago

    I ask If two or more rows have the same data (duplicated rows) because if it does you can try this:

    CREATE TABLE temp_table AS SELECT * FROM original_table:

    INSERT INTO temp_table SELECT DISTINCT * FROM original_table;

    DELETE FROM original_table;

    INSERT INTO original_table SELECT * FROM temp_table;

    DROP temp_table;

     

     

  • 13 years ago
    hey, my professor gave me a clue..there is an operation in ORACLE which we can play with id... Each and every tuple has its own id..through means of that we can delete it...i think the query may be "select rowid from {table_name}".by means of this id we can delete i think so..but now my system has been fault and no ORACLE..just execute this query and tel me plzzzz..can u post me the exact query?? plz...thanks in advance...

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.” - Rich Cook