Complex update query - need help :)

[AlexT]

Hello

I am having some problems designing an update query

Here is my current (non working) query

UPDATE dbo. myTable
SET dcStatus = 1
FROM (SELECT *
FROM myTable INNER JOIN
dbo.qStatusHelper as t1 ON
(t1.dcIdx = dbo.myTable.dcIdx))
WHERE (dcStatus < 99) AND (t1.NumMatched = t1.NumLines)

What I'd like to achieve is to set myTable.dcStatus to 1 when

a) the current value of dcStatus is less than 99

AND

b) the values NumMatched = NumLines are identical in the
qStatusHelper query, which is a subset of myTable

I'm pretty sure is possible... but I simply can't figure it out !

Any help most welcome

 

[[MVP] S.Clark]

It doesn't have to be so complex. Try to break it into multiple queries,
such that debugging is not so difficult.

Can you tell I'm not a fan of subqueries.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

 

[AlexT]

It doesn't have to be so complex. Try to break it into multiple queries,

such that debugging is not so difficult.

Well, i'd be very happy to !

What I'd like to do is to update a field in myTable based on certain
criteria from qStatusHelper, which is a complex query returning -
correctly - all records (i.e. all primary keys in myTable) which should
be updated.

However I can't updated qStatusHelper "because it contains aggregate
functions" (actually it references sub queries which contain COUNT
functions).

So it would seem that I am pretty much stuck using this sort of
syntax...

Missing something ? Any help welcome !

--alexT