+- Hack Forums (https://hackforums.net)
+-- Forum: Tech (https://hackforums.net/forumdisplay.php?fid=444)
+--- Forum: Coding (https://hackforums.net/forumdisplay.php?fid=151)
+---- Forum: Coders Lounge (https://hackforums.net/forumdisplay.php?fid=5)
+---- Thread: [DBMS] How to delete only one row from duplicate rows in Oracle SQL? (/showthread.php?tid=4459214)
How to delete only one row from duplicate rows in Oracle SQL? - [Rn] 7s^2 - Sep 23, 2014
I have the following table:
Name Address
VCR Mumbai
VCR Mumbai
Now there are 2 rows and they are duplicate.
How can I delete the second row or first row so that only one row is present?
I tried:
Delete from stud where name = 'VCR' limit 1;
But it is giving me error. Please help.
* - How bout using your brain - Sep 23, 2014
Don't you have a primary key defined?
* - stugz - Sep 23, 2014
PHP Code
<?php
delete from yourtable
where rowid not in
(select min(rowid)
from yourtable
group by name, address);
* - [Rn] 7s^2 - Sep 24, 2014
@SirRootALot No bro. Otherwise it would have been super simple.
@stugz Thanks. I'll check it out and edit the post.
EDIT: What is rowid in your code?
* - stugz - Sep 24, 2014
rowid is a pseudo-column which can be used to find the physical address of a row.
* - martin_w35 - May 1, 2015
why not just create a new table using a select statement on the original but with a distinct action.
then delete original and rename copy back to original name.
* - Ropeney - May 3, 2015
(May 1, 2015 01:04 PM)martin_w35 Wrote: why not just create a new table using a select statement on the original but with a distinct action.
then delete original and rename copy back to original name.
Because production, bad downtime, bad habbit.