[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 180
  • Last Modified:

SQL Server 2005 script help

Hi,

I have 2 tables one holding customer data, the other holding referral data, now I need to update a field based upon the referrals and this needs to be done nightly, for example

CUSTOMER (Table called contact2)
ACCOUNTNO, ULASTPROP

REFERRAL (Table called contsupp)
ACCOUNTNO, RECTYPE, DATE

Now what I need to do is make the ULASTPROP field equal to the newest date in the referral table using the accountno as the matching field. So if I take my customer entry, I may have 4 linked entries in referral table but I need the ULASTPROP to match the date of the LATEST referral.

Now I need to run through the entire customer database (approx 160,000) every night on an automated script, but im a bit stuck on where to proceeded.
0
DJMikeh
Asked:
DJMikeh
  • 2
1 Solution
 
momi_sabagCommented:
update t1
set ulastprop = max(t2.date)
from customer t1 inner join referral t2 on t1.accountno = t2.accountno
0
 
DJMikehAuthor Commented:
UPDATE t1 SET t1.ulastpropa = max(t2.lastdate)
FROM contact2 t1
INNER JOIN contsupp t2 ON t1.accountno = t2.accountno
WHERE t2.rectype='R'

When running the script above I get

An aggregate may not appear in the set list of an UPDATE statement.

Ideas? ive tried with and without the WHERE clause and it's the same each time.
0
 
DJMikehAuthor Commented:
I have solved it myself using

UPDATE contact2 SET ulastpropa = (SELECT max(lastdate) FROM contsupp WHERE contact2.accountno = contsupp.accountno AND rectype='R')
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now