Re: Delete

Article View: borland.public.interbase.sql

Article #1005

Back to borland.public.interbase.sql Previous Next

#1005

From: Svein Erling Tysvær
Date: Tue, 27 Feb 2007 08:45

27 lines
1922 bytes

If you want to delete duplicate entries (assuming ID is unique), you can 
do something like

DELETE FROM MYTABLE M1
WHERE EXISTS(SELECT * FROM MYTABLE M2
WHERE M1.PCODE = M2.PCODE
   AND M1.ID > M2.ID)

This will all duplicates but the one with the lowest ID. If you only 
want to delete the duplicate for PCODE 123 only, then you can add
AND M1.PCODE = 123. Change to AND NOT M1.ID = M2.ID if you want to 
delete both copies if the result of your code should happen to be two 
rows, but keep the row if there is only one match.

HTH,
Set

Serra wrote:
> Thank you Wayne.
> 
> SELECT ID FROM MYTABLE WHERE PCODE=123
> with above code the result could be 1 or 2 rows (record(s))
> ( never null, never 3 rows, 1 or 2 )
> i need to delete if exists both of them with below code
> i dont know the result=1 row or 2 rows.
> 
> DELETE FROM MYTABLE WHERE PCODE=........

Technical Details

Message-ID: <45e3ef67$1@newsgroups.borland.com>

Path:

arc1-ca.pugleaf.net!g2news2.google.com!news3.google.com!news4.google.com!border1.nntp.dca.giganews.com!nntp.giganews.com!newsfeed00.sul.t-online.de!newsfeed01.sul.t-online.de!t-online.de!newsgroups.borland.com!not-for-mail

References: <45e2e183@newsgroups.borland.com> <45e2fa4c$1@newsgroups.borland.com> <45e31abb@newsgroups.borland.com>

Show Full Headers

Complete Article Headers

X-Google-Thread: 114a0e,b4ac827e8c995f1f
X-Google-Attributes: gid114a0e,public
X-Google-Language: ENGLISH,ASCII-7-bit
Date: Tue, 27 Feb 2007 09:45:20 +0100
From: Svein Erling Tysvær
 <svein.erling.tysvaer@kreftregisteret.no>
User-Agent: Thunderbird 1.5.0.9 (Windows/20061207)
MIME-Version: 1.0
Newsgroups: borland.public.interbase.sql
Subject: Re: Delete
References: <45e2e183@newsgroups.borland.com> <45e2fa4c$1@newsgroups.borland.com> <45e31abb@newsgroups.borland.com>
In-Reply-To: <45e31abb@newsgroups.borland.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 84.49.7.130
Message-ID: <45e3ef67$1@newsgroups.borland.com>
X-Trace: newsgroups.borland.com 1172565863 84.49.7.130 (27 Feb 2007 00:44:23 -0800)
Lines: 27
Path: arc1-ca.pugleaf.net!g2news2.google.com!news3.google.com!news4.google.com!border1.nntp.dca.giganews.com!nntp.giganews.com!newsfeed00.sul.t-online.de!newsfeed01.sul.t-online.de!t-online.de!newsgroups.borland.com!not-for-mail

Reply to Parent Message