SIMILAR TUPLES

• • Reply

  14 years ago

  by karan_21584

  kirubakaran

  Chennai, India

  Joined 14 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

  Report abuse
• • Reply

  14 years ago

  by lexian

  Alexis Méndez

  , Puerto Rico

  Joined 14 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’;

  Report abuse
• • Reply

  14 years ago

  by karan_21584

  kirubakaran

  Chennai, India

  Joined 14 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..
  Report abuse
• • Reply

  14 years ago

  by lexian

  Alexis Méndez

  , Puerto Rico

  Joined 14 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.

  Report abuse
• • Reply

  14 years ago

  by karan_21584

  kirubakaran

  Chennai, India

  Joined 14 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?
  Report abuse
• • Reply

  14 years ago

  by karan_21584

  kirubakaran

  Chennai, India

  Joined 14 years ago
  y YAAR...no reply for my thread??? dont the answer for anyone?
  Report abuse
• • Reply

  14 years ago

  by lexian

  Alexis Méndez

  , Puerto Rico

  Joined 14 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;

   

   

  Report abuse
• • Reply

  14 years ago

  by karan_21584

  kirubakaran

  Chennai, India

  Joined 14 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...
  Report abuse