hai, i m new to this site and my 1st post is this. I have a table which consist of some tuples. i didnt set any primary key. by mistakely i entered the same values for two tuples. i want to delete a single tuple without copying the table to another new table or using DISTINCT keyword... how its possible. My professor told there is an way... and ask me to find out. CAN anyone help me? thanks in advance....CLEAR MY DOUBT IF U GENIUS
karan_21584 0
Light Poster
Recommended Answers
Jump to PostYou could use the rowid, it's probably the simplest solution. For info the rowid will uniquely identify a row within a table. It is a virtual column that Oracle maintains for all tables. Other than for situations where no primary or unique exists it shouldn't be relied upon too heavily …
Jump to PostIf you add the rowid pseudo column to your initial select statement you would see that it is different for both rows. The where clause of your update statement would then reference the rowid of the record you want to update.
Jump to PostFor small sample sizes that is the best way, but for a more generic and scalable approach I would go with the following
DELETE
FROM tablename
WHERE rowid = (SELECT t.rowid
FROM tablename t,
(SELECT keyval, min(rowid) as keeprowid
FROM tablename
GROUP BY keyval) k
WHERE t.rowid(+) = k.keeprowid
…
All 9 Replies
davidcairns 1
Junior Poster
Memento
commented:
Right on the money +1
karan_21584 0
Light Poster
Memento 2
Light Poster
davidcairns 1
Junior Poster
Memento 2
Light Poster
~s.o.s~ 2,560
Failure as a human Team Colleague Featured Poster
davidcairns 1
Junior Poster
~s.o.s~ 2,560
Failure as a human Team Colleague Featured Poster
davidcairns 1
Junior Poster
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.