Help need for Query

17 May 2005 7:18 AM

Prabhat

Hi All,

I have "Donor" and "Donations" Table.

Donor
--------
DonorID PhoneNum FirstName LastName SourceID Address1 Address2
State
----------------------------------------------------------------------------
---------

Donations
-----------
DonationID DonorID DatePaid AmountPaid SourceID
----------------------------------------------------------------

I want to update the Donor table's SourceID field with the Donation SourceID
where the Donor belongs to "CA" STATE and the Donor SourceID is Different
from the Earliest PAID Donations SourceID.

I want also the list of Donor that gets Updated.

I am using the Query but I think this is not Correct. Can any one Help me in
that?

SELECT A.DONORID, A.PHONENUM, A.FIRSTNAME, A.LASTNAME, A.SOURCEID,
B.SOURCEID
FROM DONOR A LEFT JOIN DONATIONS B ON A.DONORID = B.DONORID
WHERE A.STATE = 'CO'
AND B.DATEPAID = (SELECT MIN(DATEPAID) FROM DONATIONS WHERE DONORID =
A.DONORID)

Thanks
Prabhat

17 May 2005 7:55 AM

Rob Farley

No point in using a 'left join' here. Use 'inner join' instead. You're not
interested in rows that don't have a corresponding 'donations' entry.

And of course, if you're wanting to update 'CA' entries, then 'CO' won't do
it for you. ;)

If you want the list of donors that are getting updated, then run a select
query first.

If you put "and not a.sourceid = b.sourceid", then that will list the ones
you want to update. So then your update query has written itself:

UPDATE A SET SOURCEID = B.SOURCEID
FROM DONOR A JOIN DONATIONS B ON A.DONORID = B.DONORID
WHERE A.STATE = 'CA'
AND B.DATEPAID = (SELECT MIN(DATEPAID) FROM DONATIONS WHERE DONORID =
A.DONORID)
AND NOT A.SOURCEID = B.SOURCEID

Does this help?

Rob

17 May 2005 8:02 AM

Prabhat

Hi Rob - Sorry that was CA not CO.

Show quote

"Rob Farley" <RobFar***@discussions.microsoft.com> wrote in message
news:C90B2CEE-2606-443F-992A-D25DAEC6890F@microsoft.com...
> No point in using a 'left join' here. Use 'inner join' instead. You're not
> interested in rows that don't have a corresponding 'donations' entry.
>
> And of course, if you're wanting to update 'CA' entries, then 'CO' won't
do
> it for you. ;)
>
> If you want the list of donors that are getting updated, then run a select
> query first.
>
> If you put "and not a.sourceid = b.sourceid", then that will list the ones
> you want to update. So then your update query has written itself:
>
> UPDATE A SET SOURCEID = B.SOURCEID
> FROM DONOR A JOIN DONATIONS B ON A.DONORID = B.DONORID
> WHERE A.STATE = 'CA'
> AND B.DATEPAID = (SELECT MIN(DATEPAID) FROM DONATIONS WHERE DONORID =
> A.DONORID)
> AND NOT A.SOURCEID = B.SOURCEID
>
> Does this help?
>
> Rob

17 May 2005 8:04 AM

Prabhat

Hi Rob,

your query for update seems to be as per my requirement. I will test that
and let you know.

Thanks
Prabhat

Show quote

"Rob Farley" <RobFar***@discussions.microsoft.com> wrote in message
news:C90B2CEE-2606-443F-992A-D25DAEC6890F@microsoft.com...
> No point in using a 'left join' here. Use 'inner join' instead. You're not
> interested in rows that don't have a corresponding 'donations' entry.
>
> And of course, if you're wanting to update 'CA' entries, then 'CO' won't
do
> it for you. ;)
>
> If you want the list of donors that are getting updated, then run a select
> query first.
>
> If you put "and not a.sourceid = b.sourceid", then that will list the ones
> you want to update. So then your update query has written itself:
>
> UPDATE A SET SOURCEID = B.SOURCEID
> FROM DONOR A JOIN DONATIONS B ON A.DONORID = B.DONORID
> WHERE A.STATE = 'CA'
> AND B.DATEPAID = (SELECT MIN(DATEPAID) FROM DONATIONS WHERE DONORID =
> A.DONORID)
> AND NOT A.SOURCEID = B.SOURCEID
>
> Does this help?
>
> Rob

17 May 2005 9:34 AM

Prabhat

Hi Rob,

I think the Query is working - I have to verify is that updating correct
Donor or NOT?

But Now Can Include another condition in the same Update that will do the
below one: -

If the OLDEST PAID Donation SourceID = "PA39" or "PA43" then that time the
Donor SourceID should be updated as "PA12"

Thanks
Prabhat

Show quote

"Rob Farley" <RobFar***@discussions.microsoft.com> wrote in message
news:C90B2CEE-2606-443F-992A-D25DAEC6890F@microsoft.com...
> No point in using a 'left join' here. Use 'inner join' instead. You're not
> interested in rows that don't have a corresponding 'donations' entry.
>
> And of course, if you're wanting to update 'CA' entries, then 'CO' won't
do
> it for you. ;)
>
> If you want the list of donors that are getting updated, then run a select
> query first.
>
> If you put "and not a.sourceid = b.sourceid", then that will list the ones
> you want to update. So then your update query has written itself:
>
> UPDATE A SET SOURCEID = B.SOURCEID
> FROM DONOR A JOIN DONATIONS B ON A.DONORID = B.DONORID
> WHERE A.STATE = 'CA'
> AND B.DATEPAID = (SELECT MIN(DATEPAID) FROM DONATIONS WHERE DONORID =
> A.DONORID)
> AND NOT A.SOURCEID = B.SOURCEID
>
> Does this help?
>
> Rob

17 May 2005 9:55 AM

David Portas

Please don't repost when you still have an active thread on the topic.
It's unnecessary and inconsiderate to do so because people are going to
waste time duplicating the efforts of others in another thread.

Also, please read the article I linked in my reply in your previous
thread. You'll get faster and better answers if you follow the given
formula. In your repost you still haven't included any DDL. You just
gave us a list of column names without any of the datatypes, keys,
constraints, sample data or required results that would have helped us
solve the problem.

--
David Portas
SQL Server MVP
--

17 May 2005 11:22 PM

Hugo Kornelis

On Tue, 17 May 2005 15:04:25 +0530, Prabhat wrote:

>Hi Rob,
>
>I think the Query is working - I have to verify is that updating correct
>Donor or NOT?
>
>But Now Can Include another condition in the same Update that will do the
>below one: -
>
>If the OLDEST PAID Donation SourceID = "PA39" or "PA43" then that time the
>Donor SourceID should be updated as "PA12"

Hi Prabhat,

If Rob's query is working as you want it, then check if the following
addition helps to satisfy the extra requirement:

UPDATE A
SET    SOURCEID = CASE WHEN B.SOURCEID IN ('PA39', 'PA43')
                       THEN 'PA12'
                       ELSE B.SOURCEID
                  END
FROM   DONOR A
JOIN   DONATIONS B
ON   A.DONORID = B.DONORID
WHERE A.STATE = 'CA'
AND    B.DATEPAID = (SELECT MIN(DATEPAID)
                     FROM   DONATIONS
                     WHERE DONORID = A.DONORID)
AND    A.SOURCEID <> B.SOURCEID

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)

18 Jan 2005 11:41 AM

Prabhat

Hi Hugo,

Yes that is working - I Tryed yesterday and it work fine. Thanks for your
help.

Thanks
Prabhat

Show quote

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:d1vk81pk6nf95pg85823hpj4nmoo1bl0tu@4ax.com...
> On Tue, 17 May 2005 15:04:25 +0530, Prabhat wrote:
>
> >Hi Rob,
> >
> >I think the Query is working - I have to verify is that updating correct
> >Donor or NOT?
> >
> >But Now Can Include another condition in the same Update that will do the
> >below one: -
> >
> >If the OLDEST PAID Donation SourceID = "PA39" or "PA43" then that time
the
> >Donor SourceID should be updated as "PA12"
>
> Hi Prabhat,
>
> If Rob's query is working as you want it, then check if the following
> addition helps to satisfy the extra requirement:
>
> UPDATE A
> SET    SOURCEID = CASE WHEN B.SOURCEID IN ('PA39', 'PA43')
>                        THEN 'PA12'
>                        ELSE B.SOURCEID
>                   END
> FROM   DONOR A
> JOIN   DONATIONS B
>   ON   A.DONORID = B.DONORID
> WHERE A.STATE = 'CA'
> AND    B.DATEPAID = (SELECT MIN(DATEPAID)
>                      FROM   DONATIONS
>                      WHERE DONORID = A.DONORID)
> AND    A.SOURCEID <> B.SOURCEID
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Help need for Query - Urgent