RE: [sqlite] Re: Deleting rows with duplicate in one field

[RB Smissaert]

Thanks, that works.
Couldn't get it to work with an alias t1:

delete from table1 t1
where exists (
  select * from table1 t2
  where t1.fieldB = t2.fieldB and t1.OID <> t2.OID

Any what is wrong with that?

Also it is a bit slow despite an index of fieldB. Any ideas how to speed
this up?

RBS

-----Original Message-----
From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Sent: 04 December 2006 23:42
To: SQLite
Subject: [sqlite] Re: Deleting rows with duplicate in one field

RB Smissaert <[EMAIL PROTECTED]>
wrote: 
> Trying to delete rows in a SQLite table where
> One particular field has a duplicate value.
> 
> Tried this query, using a self-join:
> 
> DELETE
> FROM
> table1 t1 INNER JOIN table1 t2 ON
> (t1.fieldB = t2.fieldB)
> WHERE
> t1.OID <> t2.OID
> 
> But I get an error near t1

delete from table1
where exists (
  select * from table1 t2
  where table1.fieldB = t2.fieldB and table1.OID <> t2.OID
);

Igor Tandetnik

----------------------------------------------------------------------------
-
To unsubscribe, send email to [EMAIL PROTECTED]
----------------------------------------------------------------------------
-




-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------