sql server programming: You'd need to define 'recent". Do you mean the "most-recent"? What if
there were multiple donations on the same PledgeDate and there were multiple
Sources for that DonorID on that date?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
..
[quoted text, click to view]
Hi All,

I have the Below 2 tables:

Donor
=====
DonorID Int -> Primary Key
LastName
FirstName
State
Source Varchar(5)

Donations
========
DonationID Int -> Primary Key
DonorID Int
PledgeDate
DatePaid
Source Varchar(5)

And I Need to Update / Do the folowing:
=============================
Update the "Source" field of "Donor" table to "Source" of Donations Table,
for the Donors that are from STATE IN ('AB', 'BC', 'CD')
and Recent Donations are NOT from the Source = 'XX'.

Note:-
1) Recent Donation can be checked by using "DatePaid" Field of Donations
table
2) Donations are not from the Source = 'XX' can be checked using :
"Source" Filed of Donations table for that Recent Donation.

Help me to build the QUERY to: Update the Source of Donor with the Source of
Donations for the above condition.

Let me know If any one has any doubt. I need it urgent. Please Help.

Thanks
Prabhat

You can try this:

UPDATE Donor
SET source =
(SELECT COALESCE(MIN(source),Donor.source)
FROM Donations AS D
WHERE donorid = Donor.donorid
AND NOT EXISTS
(SELECT *
FROM Donations
WHERE donorid = D.donorid
AND datepaid > D.datepaid))
WHERE state IN ('AB', 'BC', 'CD')

As per a previous post of yours on a similar topic
(http://www.google.com/groups?selm=KaSdnXBxFtB8TEfdRVn-ig%40giganews.com)
it's best to avoid redundant duplication of data between tables. You can
create a view to show the same information and thereby avoid potential
update anomalies and the overhead of having to keep the duplicated data
up-to-date.

Note also that you'll usually get a quicker and better answer to your
problems if you include DDL and maybe a few rows of sample data too:
http://www.aspfaq.com/5006

--
David Portas
SQL Server MVP
--

Hi All,

I have the Below 2 tables:

Donor
=====
DonorID Int -> Primary Key
LastName
FirstName
State
Source Varchar(5)

Donations
========
DonationID Int -> Primary Key
DonorID Int
PledgeDate
DatePaid
Source Varchar(5)

And I Need to Update / Do the folowing:
=============================
Update the "Source" field of "Donor" table to "Source" of Donations Table,
for the Donors that are from STATE IN ('AB', 'BC', 'CD')
and Recent Donations are NOT from the Source = 'XX'.

Note:-
1) Recent Donation can be checked by using "DatePaid" Field of Donations
table
2) Donations are not from the Source = 'XX' can be checked using :
"Source" Filed of Donations table for that Recent Donation.

Help me to build the QUERY to: Update the Source of Donor with the Source of
Donations for the above condition.

Let me know If any one has any doubt. I need it urgent. Please Help.

Thanks
Prabhat

Hello David,

Thanks for your help.

I did not get wht for the MIN(source) IS USED. and My Donation condition
should be "Donations are NOT from the Source = 'XX'."
I am also trying to build a view to "List the donors with the "Donation
Source" for the given condition".

Thanks for the help.
Prabhat

[quoted text, click to view]

[quoted text, click to view]

Because maybe there could be more than one different Source for the latest
date. You didn't specify so I lazily chose MIN() to pick an arbitrary
(minimum) Source if that ever happens. If you want something different then
you'll have to give us more info: what to do if there are multiple sources
on the same date?

[quoted text, click to view]

Just add the WHERE clause to the subquery.

--
David Portas
SQL Server MVP
--

Hello David!

Yes You are correct. I did that as per your suggestion.

Thanks again
Prabhat

[quoted text, click to view]