issue using min function within update statement

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

SQL Server 2000 Forums

SQL Server Development (2000)

issue using min function within update statement

Author

Topic

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2009-03-04 : 15:58:34

Here is my small sql statement -


update tblinvoicehdr
set tblInvoiceHdr.InHomeAging= (select min(inhomedate) from tblinserts  where tblInserts.InvoiceNo = tblInvoiceHdr.InvoiceNo)
from tblinvoicehdr;


UPDATE tblinvoicehdr INNER JOIN tblInserts ON tblinvoicehdr.InvoiceNo = tblInserts.InvoiceNo SET tblinvoicehdr.InHomeAging = Min([tblinserts].[inhomedate]);

I have tried rewording it several times and using joins but keep running into problems either syntax errors or errors regarding that tblinvoicehdr.inhomeaging is not a part of aggregate function.

All I am trying to do is go through where invoiceno are equal to take the min(tblinserts.inhomedate) and set it in to the tblinvoicehdr.inhomeaging field.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-03-04 : 16:10:53

Can't currently test (it's quitting time - woohoo!) but it does pass the parser and should work.

update h
set h.InHomeAging = i.inhomedate
from tblinvoicehdr h
inner join tblInserts i on h.InvoiceNo = i.InvoiceNo
where i.inhomedate = (select min(inhomedate) from tblinserts where tblInserts.InvoiceNo = h.InvoiceNo)

Edit: I'll try to test it from home this evening.

Terry

-- Procrastinate now!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-04 : 16:15:03


update tblinv
set tblInv.InHomeAging= tblinvh.Minhome
from tblinvoicehdr tblinv  inner join
(Select InvoiceNo,min(inhomedate)as Minhome from tblinserts 
 Group by InvoiceNo)as tblinvh
On tblinv.InvoiceNo = tblinvh.InvoiceNo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources