Nest query - SQL Server Forums

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

Nest query

Author

Topic

dzabor
Posting Yak Master

138 Posts

Posted - 2009-08-29 : 13:38:52

I have two tables:

Name (ID, Name, Paid_Thru)
Activity(ID, Activity, Activity_Type, Thru_Date)

The Activity table has one line item for each dues payment. The max Thru_date should match the Name.Paid_Thru date. I created a script to update all records to look for the max activity_date and update the Name.paid_thru date. This works, but some records get manually updated for various reasons so i do not want to update all. I want to only update the records where the Name.Paid_Thru date is either blank or less than the max activity.thru_date.

How do I add that to my script below?

UPDATE NAME
SET PAID_THRU = (select max (activity.thru_date)
FROM ACTIVITY
where Activity_type = 'DUES'
AND Name.ID = Activity.ID)

Thanks,
DZ

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-29 : 16:50:39

[code]UPDATE A
SET A.PAID_THRU = CASE WHEN (A.PAID_THRU IS NULL OR A.PAID_THRU < B.THRU_DATE) THEN B.THRU_DATE ELSE A.PAID_THRU END
FROM NAME A
INNER JOIN (SELECT ID,MAX(THRU_DATE) FROM ACTIVITY GROUP BY ID) B
ON A.ID = B.ID
WHERE B.ACTIVITY_TYPE = 'DUES'[/code]

dzabor
Posting Yak Master

138 Posts

Posted - 2009-08-29 : 23:14:07

I see what this id doing, but it is throwing an error:

Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 'B'.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-31 : 00:45:46

[code]
UPDATE A
SET A.PAID_THRU = CASE WHEN (A.PAID_THRU IS NULL OR A.PAID_THRU < B.THRU_DATE) THEN B.THRU_DATE ELSE A.PAID_THRU END
FROM NAME A
INNER JOIN (SELECT ID,MAX(THRU_DATE) THRU_DATE FROM ACTIVITY WHERE ACTIVITY_TYPE = 'DUES' GROUP BY ID) B
ON A.ID = B.ID
[/code]

dzabor
Posting Yak Master

138 Posts

Posted - 2009-08-31 : 19:07:53

That worked - thanks!

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-09-01 : 04:59:28

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources