Update a field based upon another table

[TheMikeh]

Hi,

I have two tables (structures as below) and I need to update the first one to match a date from the second table. The tables I have are

CONTACT2
accountno | lastdate

CONTSUPP
id | accountno | rectype | lastdate

Now in this for every record within contact2 there can be 0 to unlimited records with a matching accountno.

Now what I need to do is update CONTACT2's 'lastdate' so that it is equal to the newest date from contsupp whereby the rectype is equal to 'R'

I need to run this automatically every night, theres approx 50,000 records in contact2 and about 340,000 in contsupp.

Can anyone help?

UPDATE t1 SET t1.ulastpropa = max(t2.lastdate)
FROM contact2 t1
INNER JOIN contsupp t2 ON t1.accountno = t2.accountno
WHERE t2.rectype='R'

I did try that but failed miserably ....


Many thanks
Mike

 

[Crowley16]

update t1
set col = (
select max(date)
from t2
where t1.fk = t2.pk
)

or something along those lines...

if you're using 05 then you can even use a cte...

--------------------
Procrastinate Now!

 

[TheMikeh]

Hi,

Many thanks the following seems to have done the trick!

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


Thanks
Mike

 

[djj55]

You could always do it the long way

SELECT accountno, MAX(lastdate) as lastdate 
INTO #Temp
FROM CONTSUPP 
WHERE rectype = 'R'
GROUP BY accountno 

UPDATE t1 
SET t1.ulastpropa = t2.lastdate
FROM contact2 t1
INNER JOIN #Temp t2 ON t1.accountno = t2.accountno

but I would give TheMikeh a try. (Crowley16 forgot the rectype = 'R')

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!