Re: Need Help With Delete Syntax

well, i guess there are two probable problems with this query: 1) what happens if sub-query returns more than one addresses 2) and are a.field2 and b.field2 of the same case?

try this one:

  delete from address_1 a
  where a.field_1 IN (select b.field_1 from address_2 b

                       and UPPER(substr(a.field_2,1,10)) =
                       UPPER(substr(b.field_2,1,10)));

the IN operator would handle multiple frild_1 values returned by inner query and the UPPER function would convert both a.field_2 and b.field2 to upper case for comparison.

HTH, :) A

In article <39F8D8BB.D7D15A16_at_linkline.com>,   Socalman <socalman_at_linkline.com> wrote:
> I must be brain dead because I have done this in the past. I have two
> tables in Oracle that are identical, tables "address_1" & "address_2".
> I want to delete any duplicate addresses that may be on address_1 and
> address_2. So, I thought the syntax would be something like this:
>
> delete from address_1 a
> where a.field_1 = (select b.field_1 from address_2 b
> and substr(a.field_2,1,10) =
> substr(b.field_2,1,10));
>
> Please help if you can. I know it's a simple problem, but I'm running
> on empty!
>
> Thanks, email: socalman_at_linkline.com
>
>

--

getting the meanin' of data...


Sent via Deja.com http://www.deja.com/
Before you buy.