homepage Welcome to WebmasterWorld Guest from 54.145.172.149
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Duplicate rows
StoutFiles

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4055250 posted 4:14 pm on Jan 6, 2010 (gmt 0)

If I have two rows of data in my database that are exactly the same, is it possible to call a delete statement that would only delete one row?

 

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4055250 posted 4:32 pm on Jan 6, 2010 (gmt 0)

Do you have an identity field on the table?

StoutFiles

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4055250 posted 4:57 pm on Jan 6, 2010 (gmt 0)

Nope. Exactly the same.

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4055250 posted 5:27 pm on Jan 6, 2010 (gmt 0)

Easiest way would be to add an identity field.

Another option would be to manually remove one of the rows if you have a client viewer (e.g., Server Management Studio for MS SQL).

In MS SQL you could use a CURSOR to go through and delete one of the rows. Not sure is MySQL has anything similar (sort of like a looping routine).

A brute force method is to delete both rows and re-insert the data.

StoutFiles

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4055250 posted 5:56 pm on Jan 6, 2010 (gmt 0)

Wish I could, it's an Oracle table. I was hoping maybe a LIMIT statement would work but no dice.

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4055250 posted 6:18 pm on Jan 6, 2010 (gmt 0)

Why can't you add a field? I believe Oracle supports cursors, so any of the other 3 options should work.

StoutFiles

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4055250 posted 6:23 pm on Jan 6, 2010 (gmt 0)

Can't use ALTER statements due to having a low version of Oracle. Not something I can change. :(

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4055250 posted 6:41 pm on Jan 6, 2010 (gmt 0)

ALTER TABLE is one of the most basic SQL commands... It must really be an ancient version! Does it store its data on stone tablets? :)

StoutFiles

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4055250 posted 1:25 pm on Jan 7, 2010 (gmt 0)

It's Oracle 7 on a VMS system. So yes, stone tablets are in full effect.

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4055250 posted 4:54 pm on Jan 7, 2010 (gmt 0)

Wow- does something that old even have enough memory for 2 rows? :)

Anyway, I think your best option is to delete the offending rows and re-add the data.

syber

10+ Year Member



 
Msg#: 4055250 posted 4:42 pm on Jan 10, 2010 (gmt 0)

You would have to use multiple statements:

SELECT INTO temptable <rows that are duplicates>
FROM mytable

DELETE FROM mytable <rows that are duplicates>

INSERT INTO mytable
SELECT DISTINCT *
FROM temptable

whoisgregg

WebmasterWorld Senior Member whoisgregg us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4055250 posted 12:49 am on Jan 13, 2010 (gmt 0)

Once you get it cleaned up, you may want to put a UNIQUE key on multiple columns so that you don't insert duplicate rows in the first place. With this in place, you will get an error when you try to insert a row that already exists:

ALTER TABLE `table` ADD UNIQUE INDEX `unique_index` (`column_one`, `column_two`, `column_three`);

For MySQL, you can even let the creation of this index delete the duplicates, just make sure you backup your data first in case you don't have the correct columns in the index!

ALTER IGNORE TABLE `table` ADD UNIQUE INDEX `unique_index` (`column_one`, `column_two`, `column_three`);

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4055250 posted 12:54 am on Jan 13, 2010 (gmt 0)

I guess you missed the message mentioning that ALTER TABLE isn't an option? :)

whoisgregg

WebmasterWorld Senior Member whoisgregg us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4055250 posted 1:14 am on Jan 13, 2010 (gmt 0)

Yes, yes I did miss that. Whoops. :/

/heading home after an apparently long day

StoutFiles

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4055250 posted 1:39 am on Jan 13, 2010 (gmt 0)

By adding another column I'd have to edit the Fortran and .sfo files that interact with the table...assuming I could easily add another column.

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4055250 posted 4:23 pm on Jan 13, 2010 (gmt 0)

Are you sure about that? Just because a column is in a table doesn't mean you have to read it in every SELECT that you run.

I think you may be making things overly complicated- can you provide some more details about the whole process?

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved