Prev: A couple of text parsing questions
Next: Question on Query Comparing 2 values
From: Kevin on 10 Jul 2008 11:11
I'm trying to UPDATE the value of a COUNT() operation into a field viz.
UPDATE <table1> SET <field> = ( SELECT COUNT(*) FROM <table2>
WHERE <table1.PrimaryKey> = 1;

When I do this I always get the error message
"Operation must use an updateable query".

Anybody know how to do this correctly?

thanks,
From: Allen Browne on 10 Jul 2008 11:32
Perhaps a DCount() would work:

UPDATE table1 SET field1 = DCount("*", "table2")
WHERE table1.PK = 1;

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kevin" <Kevin(a)discussions.microsoft.com> wrote in message
news:7E4BA7BC-3063-4F93-B14A-CCF8566EED55(a)microsoft.com...
> I'm trying to UPDATE the value of a COUNT() operation into a field viz.
> UPDATE <table1> SET <field> = ( SELECT COUNT(*) FROM <table2>
> WHERE <table1.PrimaryKey> = 1;
>
> When I do this I always get the error message
> "Operation must use an updateable query".
>
> Anybody know how to do this correctly?
>
> thanks,
From: bcap on 10 Jul 2008 11:37
In general SQL terms you ARE doing it correctly. This insistence by Access
that you can't use non-updateable sub-queries in an UPDATE query is one of
it's most annoying "features".

You could probably get away with this (it doesn't smell so bad if you hold
your nose):

UPDATE <table1> SET <field> = DCount("some_field", "<table2>") WHERE
<table1.PrimaryKey> = 1;

"Kevin" <Kevin(a)discussions.microsoft.com> wrote in message
news:7E4BA7BC-3063-4F93-B14A-CCF8566EED55(a)microsoft.com...
> I'm trying to UPDATE the value of a COUNT() operation into a field viz.
> UPDATE <table1> SET <field> = ( SELECT COUNT(*) FROM <table2>
> WHERE <table1.PrimaryKey> = 1;
>
> When I do this I always get the error message
> "Operation must use an updateable query".
>
> Anybody know how to do this correctly?
>
> thanks,


From: Kevin on 10 Jul 2008 11:43
Excellent suggestion. Works like a charm. Thanks much.

"Allen Browne" wrote:

> Perhaps a DCount() would work:
>
> UPDATE table1 SET field1 = DCount("*", "table2")
> WHERE table1.PK = 1;
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Kevin" <Kevin(a)discussions.microsoft.com> wrote in message
> news:7E4BA7BC-3063-4F93-B14A-CCF8566EED55(a)microsoft.com...
> > I'm trying to UPDATE the value of a COUNT() operation into a field viz.
> > UPDATE <table1> SET <field> = ( SELECT COUNT(*) FROM <table2>
> > WHERE <table1.PrimaryKey> = 1;
> >
> > When I do this I always get the error message
> > "Operation must use an updateable query".
> >
> > Anybody know how to do this correctly?
> >
> > thanks,
>
 | 
Pages: 1
Prev: A couple of text parsing questions
Next: Question on Query Comparing 2 values