Re: [SQL update question]

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Max (maxl_at_msn.com)
Date: 12/13/04 

Date: 12 Dec 2004 21:10:25 -0800

1. a) select count(invno) from invchg where invno
= invinfo.invno group by invno' is returning the Null.
b) the following will remove the run-time error,
but are not the results you are seeking:
Update invinfo Set markseqno = IsNull((Select Count(invno)
>>From invchg
Join invinfo On invchg.invno = invinfo.invno
Group by invno), 0)

2. the only way your going to get a value inserted is
to insert it. Updates don't do Inserts unless you
have an Update Trigger that does them for you.

3. the following may solve the problem for you:
Update invinfo Set markseqno = IsNull((Select Count(invno)
>>From invchg
Join invinfo On invchg.invno = invinfo.invno
Group by invno), 0)

Insert invinfo (invchg.invno, markseqno)
Select invno, 0
>>From invchg
Left Join invinfo On invchg.invno = invinfo.invno
Where invinfo.invno Is Null
        Group By invchg.invno