One Statement Update - Join, no cursor ?

**Jacco Schalkwijk** · Jul 20 '05, 01:33 AM

Re: One Statement Update - Join, no cursor ?

UPDATE A SET
Domain = (SELECT TOP 1 DOMAIN FROM B WHERE B.account = A.Account ORDER BY
B.rank)

"Dave" <SuperDrax@hotm ail.com> wrote in message
news:3ab32717.0 309191254.16196 3ea@posting.goo gle.com...[color=blue]
> HI All
>
> I have a process that I am trying to accomplish with one statement. I
> cannot think of any way to do it other than using a cursor.
>
> I was wondering if anyone could point me in the right direction.
>
> I want to update the Domain in Table A with the Domain in Table B
> where A.Account = B.Account with the highest rank.
>
>
> ----------------------------------
> Table A
> ----------------------------------
> ----------------------------------
> Account | Domain
> ----------------------------------
> ----------------------------------
> Micorsoft |null
> ----------------------------------
> IBM |null
>
>
>
> -------------------------------------------------------------
> TAble B
> -------------------------------------------------------------
> -------------------------------------------------------------
> Account | Domain | Rank
> -------------------------------------------------------------
> -------------------------------------------------------------
> Micorsoft |microsoft.com | 9
> -------------------------------------------------------------
> Micorsoft |yahoo.com | 2
> -------------------------------------------------------------
> Micorsoft |hotmail.com | 1
>
>
>
>
> Thanks!!![/color]

**JXStern** · Jul 20 '05, 01:33 AM

Re: One Statement Update - Join, no cursor ?

On Fri, 19 Sep 2003 22:06:05 +0100, "Jacco Schalkwijk"
<NOSPAMjaccos@e urostop.co.uk> wrote:[color=blue]
>UPDATE A SET
>Domain = (SELECT TOP 1 DOMAIN FROM B WHERE B.account = A.Account ORDER BY
>B.rank)[/color]

select max(domain) from b where ...

J.

[color=blue]
>
>
>"Dave" <SuperDrax@hotm ail.com> wrote in message
>news:3ab32717. 0309191254.1619 63ea@posting.go ogle.com...[color=green]
>> HI All
>>
>> I have a process that I am trying to accomplish with one statement. I
>> cannot think of any way to do it other than using a cursor.
>>
>> I was wondering if anyone could point me in the right direction.
>>
>> I want to update the Domain in Table A with the Domain in Table B
>> where A.Account = B.Account with the highest rank.
>>
>>
>> ----------------------------------
>> Table A
>> ----------------------------------
>> ----------------------------------
>> Account | Domain
>> ----------------------------------
>> ----------------------------------
>> Micorsoft |null
>> ----------------------------------
>> IBM |null
>>
>>
>>
>> -------------------------------------------------------------
>> TAble B
>> -------------------------------------------------------------
>> -------------------------------------------------------------
>> Account | Domain | Rank
>> -------------------------------------------------------------
>> -------------------------------------------------------------
>> Micorsoft |microsoft.com | 9
>> -------------------------------------------------------------
>> Micorsoft |yahoo.com | 2
>> -------------------------------------------------------------
>> Micorsoft |hotmail.com | 1
>>
>>
>>
>>
>> Thanks!!![/color]
>[/color]

**Jacco Schalkwijk** · Jul 20 '05, 01:33 AM

Re: One Statement Update - Join, no cursor ?

J,

That will give you the domain with the name that is the highest
alphabetically, not the highest ranking domiain. I.e. in this case you will
get yahoo.com instead of microsoft.com, which is not what Dave is looking
for.

Jacco

"JXStern" <JXSternChangeX 2R@gte.net> wrote in message
news:rbmnmv04c1 hqo6h9geeddnbgh li1976k3o@4ax.c om...[color=blue]
> On Fri, 19 Sep 2003 22:06:05 +0100, "Jacco Schalkwijk"
> <NOSPAMjaccos@e urostop.co.uk> wrote:[color=green]
> >UPDATE A SET
> >Domain = (SELECT TOP 1 DOMAIN FROM B WHERE B.account = A.Account ORDER BY
> >B.rank)[/color]
>
> select max(domain) from b where ...
>
> J.
>
>[color=green]
> >
> >
> >"Dave" <SuperDrax@hotm ail.com> wrote in message
> >news:3ab32717. 0309191254.1619 63ea@posting.go ogle.com...[color=darkred]
> >> HI All
> >>
> >> I have a process that I am trying to accomplish with one statement. I
> >> cannot think of any way to do it other than using a cursor.
> >>
> >> I was wondering if anyone could point me in the right direction.
> >>
> >> I want to update the Domain in Table A with the Domain in Table B
> >> where A.Account = B.Account with the highest rank.
> >>
> >>
> >> ----------------------------------
> >> Table A
> >> ----------------------------------
> >> ----------------------------------
> >> Account | Domain
> >> ----------------------------------
> >> ----------------------------------
> >> Micorsoft |null
> >> ----------------------------------
> >> IBM |null
> >>
> >>
> >>
> >> -------------------------------------------------------------
> >> TAble B
> >> -------------------------------------------------------------
> >> -------------------------------------------------------------
> >> Account | Domain | Rank
> >> -------------------------------------------------------------
> >> -------------------------------------------------------------
> >> Micorsoft |microsoft.com | 9
> >> -------------------------------------------------------------
> >> Micorsoft |yahoo.com | 2
> >> -------------------------------------------------------------
> >> Micorsoft |hotmail.com | 1
> >>
> >>
> >>
> >>
> >> Thanks!!![/color]
> >[/color]
>[/color]

**Dave** · Jul 20 '05, 01:33 AM

Re: One Statement Update - Join, no cursor ?

Thanks guys!!! I will give it a try Monday when I get to work

JXStern <JXSternChangeX 2R@gte.net> wrote in message news:<rbmnmv04c 1hqo6h9geeddnbg hli1976k3o@4ax. com>...[color=blue]
> On Fri, 19 Sep 2003 22:06:05 +0100, "Jacco Schalkwijk"
> <NOSPAMjaccos@e urostop.co.uk> wrote:[color=green]
> >UPDATE A SET
> >Domain = (SELECT TOP 1 DOMAIN FROM B WHERE B.account = A.Account ORDER BY
> >B.rank)[/color]
>
> select max(domain) from b where ...
>
> J.
>
>[color=green]
> >
> >
> >"Dave" <SuperDrax@hotm ail.com> wrote in message
> >news:3ab32717. 0309191254.1619 63ea@posting.go ogle.com...[color=darkred]
> >> HI All
> >>
> >> I have a process that I am trying to accomplish with one statement. I
> >> cannot think of any way to do it other than using a cursor.
> >>
> >> I was wondering if anyone could point me in the right direction.
> >>
> >> I want to update the Domain in Table A with the Domain in Table B
> >> where A.Account = B.Account with the highest rank.
> >>
> >>
> >> ----------------------------------
> >> Table A
> >> ----------------------------------
> >> ----------------------------------
> >> Account | Domain
> >> ----------------------------------
> >> ----------------------------------
> >> Micorsoft |null
> >> ----------------------------------
> >> IBM |null
> >>
> >>
> >>
> >> -------------------------------------------------------------
> >> TAble B
> >> -------------------------------------------------------------
> >> -------------------------------------------------------------
> >> Account | Domain | Rank
> >> -------------------------------------------------------------
> >> -------------------------------------------------------------
> >> Micorsoft |microsoft.com | 9
> >> -------------------------------------------------------------
> >> Micorsoft |yahoo.com | 2
> >> -------------------------------------------------------------
> >> Micorsoft |hotmail.com | 1
> >>
> >>
> >>
> >>
> >> Thanks!!![/color]
> >[/color][/color]

**Dave** · Jul 20 '05, 01:34 AM

Re: One Statement Update - Join, no cursor ?

Wow.... just as I was getting ready to give up it worked.

Thanks alot for your help!!!

Final syntax

UPDATE MainTable
SET EMAIL_DOMAIN = (SELECT TOP 1 B.DOMAIN FROM DerivedTable B where
A.NAME = B.NAME ORDER BY B.NUM DESC)
from MainTable A

Works great using a temp table or a derived table

Thanks again.

SuperDrax@hotma il.com (Dave) wrote in message news:<3ab32717. 0309200705.62fa 0673@posting.go ogle.com>...[color=blue]
> Thanks guys!!! I will give it a try Monday when I get to work
>
>
> JXStern <JXSternChangeX 2R@gte.net> wrote in message news:<rbmnmv04c 1hqo6h9geeddnbg hli1976k3o@4ax. com>...[color=green]
> > On Fri, 19 Sep 2003 22:06:05 +0100, "Jacco Schalkwijk"
> > <NOSPAMjaccos@e urostop.co.uk> wrote:[color=darkred]
> > >UPDATE A SET
> > >Domain = (SELECT TOP 1 DOMAIN FROM B WHERE B.account = A.Account ORDER BY
> > >B.rank)[/color]
> >
> > select max(domain) from b where ...
> >
> > J.
> >
> >[color=darkred]
> > >
> > >
> > >"Dave" <SuperDrax@hotm ail.com> wrote in message
> > >news:3ab32717. 0309191254.1619 63ea@posting.go ogle.com...
> > >> HI All
> > >>
> > >> I have a process that I am trying to accomplish with one statement. I
> > >> cannot think of any way to do it other than using a cursor.
> > >>
> > >> I was wondering if anyone could point me in the right direction.
> > >>
> > >> I want to update the Domain in Table A with the Domain in Table B
> > >> where A.Account = B.Account with the highest rank.
> > >>
> > >>
> > >> ----------------------------------
> > >> Table A
> > >> ----------------------------------
> > >> ----------------------------------
> > >> Account | Domain
> > >> ----------------------------------
> > >> ----------------------------------
> > >> Micorsoft |null
> > >> ----------------------------------
> > >> IBM |null
> > >>
> > >>
> > >>
> > >> -------------------------------------------------------------
> > >> TAble B
> > >> -------------------------------------------------------------
> > >> -------------------------------------------------------------
> > >> Account | Domain | Rank
> > >> -------------------------------------------------------------
> > >> -------------------------------------------------------------
> > >> Micorsoft |microsoft.com | 9
> > >> -------------------------------------------------------------
> > >> Micorsoft |yahoo.com | 2
> > >> -------------------------------------------------------------
> > >> Micorsoft |hotmail.com | 1
> > >>
> > >>
> > >>
> > >>
> > >> Thanks!!!
> > >[/color][/color][/color]

**JXStern** · Jul 20 '05, 01:34 AM

Re: One Statement Update - Join, no cursor ?

On Sat, 20 Sep 2003 15:08:50 +0100, "Jacco Schalkwijk"
<NOSPAMjaccos@e urostop.co.uk> wrote:[color=blue]
>That will give you the domain with the name that is the highest
>alphabetically , not the highest ranking domiain. I.e. in this case you will
>get yahoo.com instead of microsoft.com, which is not what Dave is looking
>for.[/color]

Oh, er, I shoulda scrolled down.

Lots of questions about what's a PK and what can have dups, ...

J.

One Statement Update - Join, no cursor ?

One Statement Update - Join, no cursor ?

Comment

Comment

Comment

Comment

Comment

Comment