Update from another table with MAX function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update from another table with MAX function

Author

Topic

Big_tim
Starting Member

22 Posts

Posted - 2009-09-10 : 06:03:11

I have a bit of a scripting issue which I'm trying to work on and have got a bit stuck.
I'm trying to update a few fields in a couple of tables and one of them I can't quite get my head around.

This select statement...

 
SELECT 
ID
, start_date
, end_date 
,      (SELECT MAX(date_received) 
        FROM _donation 
        WHERE _donation.gift_aid_declaration_id = _Gift_Aid_Declaration.id 
        AND _donation.gift_aid_status = 4) 
        AS Final_Donation_Date 

FROM 
_Gift_Aid_Declaration

WHERE
scan_file_ref is null
and event_id is null
and status = 1

Now, the problem is that I need to update the end_date so that it is the same as the Final_Donation_Date, which first of all sounded like a simple idea, but every time I try to come up with a cunning plan it seems to fall flat on it's face.

The trouble is that because of the combination of:

Data in the 2 fields are in different tables (which wouldn't normally be a problem by using WHERE EXISTS)

Because I have the MAX(date_received) in there.

If I try to use WHERE EXISTS:

 
UPDATE Donation

SET end_date =       (SELECT max (date_received) 
                           FROM _donation 
                           JOIN _Gift_aid_declaration ON _Gift_aid_declaration.id = _donation.gift_aid_declaration_id
                           WHERE _donation.gift_aid_declaration_id = _Gift_Aid_Declaration.id 
                           AND _donation.gift_aid_status = 4)
WHERE EXISTS
 (SELECT max (date_received) 
                           FROM _donation 
                           JOIN _Gift_aid_declaration ON _Gift_aid_declaration.id = _donation.gift_aid_declaration_id
                           WHERE _donation.gift_aid_declaration_id = _Gift_Aid_Declaration.id 
                           AND _donation.gift_aid_status = 4

But the bit where it fails is that because I'm now having to put a join in this select statement it is just returning the one MAX date for the entire table.

I've now run into a bit of a brick wall and can't think how to get around this, so any help that you guys could provide would be hugely appreciated!

Regards,

Tim

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-10 : 15:32:53

Can you please post a bit more clear?
I think it is easy to do that update but I am doubtful with your post...

There are table names to read in your post: _donation, _Gift_aid_declaration, Donation
You talk about columns in your post: end_date, date_received, Final_Donation_Date

Maybe you can give some sample data with table structure and wanted output?

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-13 : 14:17:22

in which table is date_received column?

Big_tim
Starting Member

22 Posts

Posted - 2009-09-14 : 04:29:33

Thanks for the responses, I've manages to resolve this now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources