Query In Error?

**Andre Kuyt** · Jul 20 '05, 01:00 AM

Re: Query In Error?

wan_fui_chan@ho tmail.com (Wanny) wrote in message news:<e55732c9. 0307290152.768f 33a1@posting.go ogle.com>...[color=blue]
> Hi There,
>
> I can't seem to see what's wrong with the query below
>
> DELETE
> FROM Users_Details UD1
> WHERE UD1.UserID = (
> SELECT TOP 1 UD2.UserID
> FROM Users_Details UD2
> WHERE UD1.useremail = UD2.useremail
> )
>
> Keeps giving me incorrect syntax on Line 1 near UD1.
> Can you not alias tables when using a Delete?
> What I'm trying to do is cleanse the table of duplicate e-mail
> addresses but I always want to leave one copy of the e-mail address
> i.e. if there are 3 of the same e-mail address then I want to remove 2
> of them.
> Anyone got a better way of doing it that doesn't use cursors then I
> would appreciate it.
>
> Cheers[/color]

Sometimes an error message is not as cryptic as it seems and actually
very helpfull. If you check SQL books online for the definition of the
DELETE statement you will notice that you can only use table aliasses
in an optional seperate FROM clause.

Fortunatly this error was a blessing in disguise as your statement
will also purge all users without a duplicate e-mail adress from the
system. Your correlated subquery fails to check that it's not actually
matching the user from the main query.

So let's try to create something that might work a little better.
Since you didn't post any DDL I'm going to guess a little, but you'll
get the idea. I'll use a subselect which will will select those
userid's to be deleted (I'm keeping the userid's with the largest
value, under the assumption that those are the most recent ones)

create table user_details
(
UserID int not null,
Useremail varchar(255) not null
)

insert into user_details values
(1,'abc@def.gh' )
insert into user_details values
(2,'ijk@lmn.op' )
insert into user_details values
(3,'qrs@tuv.wx' )
insert into user_details values
(4,'abc@def.gh' )
insert into user_details values
(5,'ijk@lmn.op' )
insert into user_details values
(6,'foo@bar.org ')
insert into user_details values
(7,'abc@def.gh' )

DELETE FROM
user_details where userid in
(
select
Distinct ToBeDeleted.use rid
FROM
user_details ToBeDeleted inner join user_details ToBeKept
on ToBeDeleted.Use rID < ToBeKept.UserID and ToBeDeleted.Use rEmail =
ToBeKept.UserEm ail
)

With Regards
Andre Kuyt

**Wanny** · Jul 20 '05, 01:00 AM

Re: Query In Error?

Hi Andre,

Thanks a lot for your help.
Your query did exactly what I needed.
I don't quite understand how the query works though and would
appreciate it if you could help me out further.
The bit I don't understand is, how does the query know to remove items
with a UserID less than the MAX(UserID).

We have the Join criteria

"on ToBeDeleted.Use rID < ToBeKept.UserID "

but it doesn't specify anywhere that it's to be less than the
MAX(UserID) so how does it do it?

I have to apologise for my ignorance if the answer is obvious and
sorry for pestering you further.

Thanks.

[color=blue]
>
> So let's try to create something that might work a little better.
> Since you didn't post any DDL I'm going to guess a little, but you'll
> get the idea. I'll use a subselect which will will select those
> userid's to be deleted (I'm keeping the userid's with the largest
> value, under the assumption that those are the most recent ones)
>
> create table user_details
> (
> UserID int not null,
> Useremail varchar(255) not null
> )
>
> insert into user_details values
> (1,'abc@def.gh' )
> insert into user_details values
> (2,'ijk@lmn.op' )
> insert into user_details values
> (3,'qrs@tuv.wx' )
> insert into user_details values
> (4,'abc@def.gh' )
> insert into user_details values
> (5,'ijk@lmn.op' )
> insert into user_details values
> (6,'foo@bar.org ')
> insert into user_details values
> (7,'abc@def.gh' )
>
> DELETE FROM
> user_details where userid in
> (
> select
> Distinct ToBeDeleted.use rid
> FROM
> user_details ToBeDeleted inner join user_details ToBeKept
> on ToBeDeleted.Use rID < ToBeKept.UserID and ToBeDeleted.Use rEmail =
> ToBeKept.UserEm ail
> )
>
>
> With Regards
> Andre Kuyt[/color]

**Andre Kuyt** · Jul 20 '05, 01:00 AM

Re: Query In Error?

wan_fui_chan@ho tmail.com (Wanny) wrote in message news:<e55732c9. 0307290830.7570 605@posting.goo gle.com>...[color=blue]
> Hi Andre,
>
> Thanks a lot for your help.
> Your query did exactly what I needed.
> I don't quite understand how the query works though and would
> appreciate it if you could help me out further.
> The bit I don't understand is, how does the query know to remove items
> with a UserID less than the MAX(UserID).
>
> We have the Join criteria
>
> "on ToBeDeleted.Use rID < ToBeKept.UserID "
>
> but it doesn't specify anywhere that it's to be less than the
> MAX(UserID) so how does it do it?
>
> I have to apologise for my ignorance if the answer is obvious and
> sorry for pestering you further.
>
> Thanks.
>
>[color=green]
> >
> > So let's try to create something that might work a little better.
> > Since you didn't post any DDL I'm going to guess a little, but you'll
> > get the idea. I'll use a subselect which will will select those
> > userid's to be deleted (I'm keeping the userid's with the largest
> > value, under the assumption that those are the most recent ones)
> >
> > create table user_details
> > (
> > UserID int not null,
> > Useremail varchar(255) not null
> > )
> >
> > insert into user_details values
> > (1,'abc@def.gh' )
> > insert into user_details values
> > (2,'ijk@lmn.op' )
> > insert into user_details values
> > (3,'qrs@tuv.wx' )
> > insert into user_details values
> > (4,'abc@def.gh' )
> > insert into user_details values
> > (5,'ijk@lmn.op' )
> > insert into user_details values
> > (6,'foo@bar.org ')
> > insert into user_details values
> > (7,'abc@def.gh' )
> >
> > DELETE FROM
> > user_details where userid in
> > (
> > select
> > Distinct ToBeDeleted.use rid
> > FROM
> > user_details ToBeDeleted inner join user_details ToBeKept
> > on ToBeDeleted.Use rID < ToBeKept.UserID and ToBeDeleted.Use rEmail =
> > ToBeKept.UserEm ail
> > )
> >
> >
> > With Regards
> > Andre Kuyt[/color][/color]

A little further clarification seems to be in order, so lets move back
to the original example data and try this little query to see what it
actually is that it is selecting:

select * from user_details ToBeDeleted inner join user_details
ToBeKept
on ToBeDeleted.Use rID < ToBeKept.UserID and ToBeDeleted.Use rEmail =
ToBeKept.UserEm ail
order by toBeDeleted.use rid

This yields the following resultset

UserID UserEmail UserID Useremail
----------- ------------ ----------- ----------
1 abc@def.gh 4 abc@def.gh
1 abc@def.gh 7 abc@def.gh
2 ijk@lmn.op 5 ijk@lmn.op
4 abc@def.gh 7 abc@def.gh

So what we see is that the condition ToBeDeleted.Use rID <
ToBeKept.UserID
actually results in at least one row in the resultset for each userid
which has at least one higher ranking userid with the same email
address.

UserID 1 actually appears twice, because it has 2 higher userids with
the same email address (4 and 7)

So what we know from this resultset is the following: If a userid
appears in the left most column, there is at least 1 userid with a
higher number in the table. As you can see for yourself UserID 7 is
excluded from the ToBeDeleted list because there is no larger userID
available. For that same reason all userid's without duplicate emails
are excluded from the ToBeDeleted results.

So if you remove all the rows with the ToBeDeleted UserID's from the
table you will be left with those records without duplicates which
have the Maximum userID

So, as you can see, the query itself actually knows nothing about a
MAX(UserID), which isn't surprising at all as queries have no brains
:)

(Advanced topic:
As a variation of this query you could use the join condition
ToBeDeleted.Use rID > ToBeKept.UserID to keep all the minimum UserID's.
)

Andre

**Wanny** · Jul 20 '05, 01:01 AM

Re: Query In Error?

Hi Andre,

Thanks again.
The explanation was excellent and I'm well on my way to being an
expert SQL programmer ;-)

Cheers

Query In Error?

Query In Error?

Comment

Comment

Comment

Comment