update query

  • Thread starter Thread starter Ivan Debono
  • Start date Start date
I

Ivan Debono

Hi all,

I have the following query:

UPDATE plans SET plan_date = (
SELECT Min(calendar.date)
FROM calendar
WHERE (((calendar.completed_yn)=False) AND ((calendar.deleted_yn)=False) AND
((calendar.parent_table)='plans') AND ((calendar.parent_id)=64))
)
WHERE plans.id = 64

What is does is that it updates the plan_date in table plans with the
smallest date in table calendar which is not deleted and not completed.

But I get an error: Operation must use an updatable query. (Error 3073)

Anyone knows why??

Thanks,
Ivan
 
I have the following query:

UPDATE plans SET plan_date = (
SELECT Min(calendar.date)
FROM calendar
WHERE (((calendar.completed_yn)=False) AND ((calendar.deleted_yn)=False) AND
((calendar.parent_table)='plans') AND ((calendar.parent_id)=64))
)
WHERE plans.id = 64

What is does is that it updates the plan_date in table plans with the
smallest date in table calendar which is not deleted and not completed.

But I get an error: Operation must use an updatable query. (Error 3073)

Anyone knows why??
Click to expand...

Because any query that contains a Min, Max, or any other Totals
operation is non-updateable... even if (as in this case) it is not a
logical constraint! It's just the way Access was implemented.

You can use the DMin() VBA function in place of the subquery:

UPDATE plans
SET plan_date = DMin("[Date]", "[calendar]", "[Completed_yn] = False
AND deleted_yn = False AND parent_table = 'Plans' and parent_id = " &
[Plans.ID])
WHERE plans.ID = <criteria>

Note that I'm suggesting using a "correlated" pseudo-subquery,
referencing the outer query's ID rather than explicitly putting 64
into both the main query criterion and the DMin().
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yes, because JET UPDATE queries a buggered.

Instead of the subquery as the SET value use a DMin() domain function.
E.g.:

UPDATE plans SET plan_date =
DMIN("[Date]", "calendar", "completed_yn=False AND deleted_yn=False AND
parent_table='plans' AND parent_id=64")
WHERE plans.id = 64

Suggestion: Change Calendar.Date column name to something else - Date
is a reserved word in VBA & SQL.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQQ6WroechKqOuFEgEQIJJQCeJgCROLbOYYkfTr03F3OiQXW6okQAoKbz
IAMeyrNxnEjJhYRBIZt0IZLL
=htTb
-----END PGP SIGNATURE-----
 
tks. it works :)

John Vinson said:
I have the following query:

UPDATE plans SET plan_date = (
SELECT Min(calendar.date)
FROM calendar
WHERE (((calendar.completed_yn)=False) AND ((calendar.deleted_yn)=False) AND
((calendar.parent_table)='plans') AND ((calendar.parent_id)=64))
)
WHERE plans.id = 64

What is does is that it updates the plan_date in table plans with the
smallest date in table calendar which is not deleted and not completed.

But I get an error: Operation must use an updatable query. (Error 3073)

Anyone knows why??
Click to expand...

Because any query that contains a Min, Max, or any other Totals
operation is non-updateable... even if (as in this case) it is not a
logical constraint! It's just the way Access was implemented.

You can use the DMin() VBA function in place of the subquery:

UPDATE plans
SET plan_date = DMin("[Date]", "[calendar]", "[Completed_yn] = False
AND deleted_yn = False AND parent_table = 'Plans' and parent_id = " &
[Plans.ID])
WHERE plans.ID = <criteria>

Note that I'm suggesting using a "correlated" pseudo-subquery,
referencing the outer query's ID rather than explicitly putting 64
into both the main query criterion and the DMin().
Click to expand...
 
Back
Top