SQL question

[Scooby]

Hi, i have a small sql problem im not sure how to solve,

ive been adding some property info to my sql table over time only to find ive been adding the info twice.

(yes my bad )

my current layout is: x,y,z,type,dim,slot,rot,pPrice,oPrice,aPrice,forSale,owner,oldOwner,oldOwnerOwed,ID

so currently it would look something like this (theres 1802 in total):

31,90,0,5,2000,9,214.7,99995,99995,0,No,Dave,None,0,1

31,90,0,5,2000,9,214.7,99995,99995,0,No,Dave,None,0,1

12,0,90,5,2001,1,122,24995,24995,0,Yes,Pete,None,0,2

12,0,90,5,2001,1,122,24995,24995,0,Yes,Pete,None,0,2

my question is, how would i go about removing one set of info but leaving the other there?

i know the usual basic query's from the wiki but im not sure how i can do this without deleting both sets of info.

[Jaysds1]

I'm kinda confused,

you want to remove 1 line of info and leave the rest of the info's.

[Scooby]

well i have every line twice... so i was hoping somehow i could remove the duplicated lines.

[NeXTreme]

So this is SQLite I guess?

If you know which columns you have duplicate:

  
executeSQLQuery("DELETE FROM tableName WHERE forSale='yes' AND owner='Dave' LIMIT 1") 
  

The "LIMIT 1" is the key here, it limits the query to affect only one row

This is what I know from MySQL, I don't know if it will work with SQLite also, I don't use it.

[SDK]

Another way is using SELECT DISTINCT, which only selects unique rows and skips the duplicates, to create a (fixed) new db.

"SELECT DISTINCT * INTO newtable FROM oldtable" 

Then drop (delete) the original table

"DROP TABLE oldtable" 

and rename the new table to the old one.

"ALTER TABLE newtable RENAME TO oldtable" 

(Found this after some googling, I'd expect it to work, but didn't test it so make sure you backup your database first)

[Scooby]

thanks guys, this looks like what i need... i'll give it a try after work.

everything i tried with the basic functions deleted both entries leaving the table blank so the 'limit' thing might be the key.

but thanks both of u for taking the time out to help.

Edit: ive tried both suggestions and had no luck with either... i get a syntax error for both LIMIT and INTO

[SDK]

Yes, has been awhile since used sqlite im afraid

The first query was wrong:

"CREATE TABLE tempTable AS SELECT DISTINCT * FROM originalTable" 
"DROP TABLE originalTable" 
"ALTER TABLE tempTable RENAME TO originalTable" 

I tested it on a commandline and it worked, no duplicates.

[Scooby]

heh no worries and thanks.

ive just finished a workaround... looking at ur querys its a very long work around haha,

but i think ive sorted it, i was bored so i wrote a small function to save each piece of data to an ID number in a table,

then dropped the sql table and created it again, then resaved the old data from the new details stored.

long way round i know but i got there in the end and if it happens again i'll be sure to remember these:

"CREATE TABLE tempTable AS SELECT DISTINCT * FROM originalTable"

"DROP TABLE originalTable"

"ALTER TABLE tempTable RENAME TO originalTable"

thanks