Updating a table based upon a second table

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

Updating a table based upon a second table

Author

Topic

Themikeh
Starting Member

2 Posts

Posted - 2009-07-10 : 06:40:51

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 | ulastpropa

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 'ulastpropa' so that it is equal to the newest lastdate 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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-10 : 06:46:53

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

Madhivanan

Failing to plan is Planning to fail

Themikeh
Starting Member

2 Posts

Posted - 2009-07-10 : 06:57:36

Thanks :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources