Help need for Query - Urgent

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
PrabhatNo 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|||Hi Rob - Sorry that was CA not CO.
"Rob Farley" <RobFarley@.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|||Hi Rob,
your query for update seems to be as per my requirement. I will test that
and let you know.
Thanks
Prabhat
"Rob Farley" <RobFarley@.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|||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
"Rob Farley" <RobFarley@.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|||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
--|||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)|||Hi Hugo,
Yes that is working - I Tryed yesterday and it work fine. Thanks for your
help.
Thanks
Prabhat
"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:
>
the
> 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)