Update query w/subquery has me stumped :s

  • Thread starter Thread starter Daedalus
  • Start date Start date
D

Daedalus

I have the SQL listed below, why doesn't it work? I get
error 'Operation must use an updateable query'. All I am
trying to do is update the ApprovedDate field in the
UniquePrograms table with the earliest ApprovedDate
listed in its corresponding entries in the ProgramHistory
table. ProgramHistory table can have multiple entries for
each program due to the fact that a program can be
approved, cancelled and reenacted multiple times. Am I
totally off on how to accomplish this? :s

UPDATE UniquePrograms AS UP
SET UP.ApprovedDate = (
SELECT Min(U.ABORApprovedDate)
FROM ProgramHistory AS PH
WHERE PH.ProgramAbr = UP.ProgramAbr AND PH.ProgramName
= UP.ProgramName
);
 
Daedalus said:
I have the SQL listed below, why doesn't it work? I get
error 'Operation must use an updateable query'. All I am
trying to do is update the ApprovedDate field in the
UniquePrograms table with the earliest ApprovedDate
listed in its corresponding entries in the ProgramHistory
table. ProgramHistory table can have multiple entries for
each program due to the fact that a program can be
approved, cancelled and reenacted multiple times. Am I
totally off on how to accomplish this? :s

UPDATE UniquePrograms AS UP
SET UP.ApprovedDate = (
SELECT Min(U.ABORApprovedDate)
FROM ProgramHistory AS PH
WHERE PH.ProgramAbr = UP.ProgramAbr AND PH.ProgramName
= UP.ProgramName
);
Click to expand...

Hi Daedalus,

You have the choice of 3 workarounds
(if I have learned correctly from this ng...
so make backup of db before trying any
of the *untested* following)

1) Use DMin

UPDATE UniquePrograms AS UP
SET UP.ApprovedDate =
DMin("ABORApprovedDate", "ProgramHistory",
"ProgramAbr = '" & UP.ProgramAbr & "' AND
ProgramName = '" & UP.ProgramName & "'")

2) Turn your subquery in to a make table query
and run update query joining to this temp table.

SELECT
ProgramAbr,
ProgramName,
Min(ABORApprovedDate) As MinDate
INTO tblTemp
FROM
ProgramHistory
GROUP BY
ProgramAbr,
ProgramName;

UPDATE UniquePrograms AS UP
INNER JOIN tblTemp AS t
ON UP.ProgramAbr = t.ProgramAbr
AND UP.ProgramName = t.ProgramName
SET UP.ApprovedDate = t.MinDate;

3) Use "elementary arithmetic" aggregation

UPDATE UniquePrograms AS UP
INNER JOIN ProgramHistory AS PH
ON UP.ProgramAbr = PH.ProgramAbr
AND UP.ProgramName = PH.ProgramName
SET UP.ApprovedDate =
IIF(PH.ABORApprovedDate<UP.ApprovedDate,
PH.ABORApprovedDate, UP.ApprovedDate);

This last technique assumes all UP.ApprovedDate
have some initial value that will not be < the min
that you are trying to find in PH so may not work
or you may need to intialize UP.ApprovedDate
to some "for-sure too-large date" before running
this query....then check for anomolies, i.e.,

prequery:

UPDATE UniquePrograms AS UP
SET UP.ApprovedDate = #9/9/9999#;

This would obviously be bad if you have some
"valid min dates" for programs that will
not be in ProgramHistory....you know
your data best.

Please repond back if I have misunderstood.

Good luck,

Gary Walter
 
I have the SQL listed below, why doesn't it work? I get
error 'Operation must use an updateable query'.
Click to expand...

It doesn't work because JET instantly marks any query with a GROUP BY
or any other aggregate function as non-updateable, even if (as in this
case) there is no logical reason to do so.

The getaround is to use the DMax() *function* rather than a subquery
to select the desired row.
 
Back
Top