“Operation must use an updateable query" error.

[Freezerbird]

I have a table of results from soccer matches (MatchTeams) where each record represents a team in a match and the number of goals they scored. In other words, there are two records for each soccer match, one for the home team and one for the away team (the primary key is a MatchTeamID field).

For each record, I want to assign a number of points based on whether they won or lost the match (2 points for a win, 1 for a draw, 0 for a defeat). This means for each record comparing MatchTeams.Score with the other record that has the same MatchTeamID. I’ve used an IIF statement for this, which works fine in a SELECT query.

My problem is, I actually want to update each record in MatchTeams with these points, in a field MatchTeams.Points. When I use the same IIF statement in an UPDATE query, I get this “operation must use an updateable query" error.

UPDATE MatchTeams SET MatchTeams.Points = IIf([MatchTeams].[Score]=(SELECT Dupe1.Score FROM MatchTeams AS Dupe1 WHERE Dupe1.MatchID=MatchTeams.MatchID AND Dupe1.MatchTeamID<>MatchTeams.MatchTeamID),1,IIf([MatchTeams].[Score]>(SELECT Dupe2.Score FROM MatchTeams AS Dupe2 WHERE Dupe2.MatchID=MatchTeams.MatchID AND Dupe2.MatchTeamID<>MatchTeams.MatchTeamID),2,0));

I don’t get why MatchTeam.Points is not updateable. Any help please?

 

[raskew]

Hi -

You need to post your query SQL.

Bob

 

[Rabbie]

See this link for some reasons why a query is not updateable

 

[ajetrumpet]

See this link for some reasons why a query is not updateable

OR....see this one.

 

[Freezerbird]

Many thanks for your replies (Adam's was particularly helpful ). Naturally I've already had a look for solutions elsewhere but I'm still not clear about what the problem is.

Here is my SQL:

UPDATE MatchTeams SET MatchTeams.Points = IIf([MatchTeams].[Score]=(SELECT Dupe1.Score FROM MatchTeams AS Dupe1 WHERE Dupe1.MatchID=MatchTeams.MatchID AND Dupe1.MatchTeamID<>MatchTeams.MatchTeamID),1,IIf([MatchTeams].[Score]>(SELECT Dupe2.Score FROM MatchTeams AS Dupe2 WHERE Dupe2.MatchID=MatchTeams.MatchID AND Dupe2.MatchTeamID<>MatchTeams.MatchTeamID),2,0));

The only possible issue on the Allen Browne list that seems relevant to me is the subquery in the SET clause. Are the sub-selects in the IIF statements the cause of the problem?

I don't get why MatchTeams is not updateable, because it's just a table, not a query. OK, I'm using a query to update it, but I'm updating the table, not the query. Or am I just being thick?

Many thanks
Dan

 

[LPurvis]

You're not being thick - but it's something you have to accept with Jet.
Whereas other engines (e.g. SQL Server) can maintain updatability when only referencing a non-updatable source, Jet requires that every part of a query be updatable for the entire query to be updatable.

e.g. consider the (contrived and poorly normalised) example
UPDATE tblEmployees SET DealsCount = (SELECT COUNT(*) FROM tblDeals D WHERE D.EmployeeID = tblEmployees.EmployeeID)

That will fail under Jet but execute under SQL Server.
The Jet workaround is to use VBA to extend its abilities (as is so often the case).

UPDATE tblEmployees SET DealsCount = DCount("*", "tblDeals", "EmployeeID = " & tblEmployees.EmployeeID)

Or write your own user defined VBA function to use in its place (the domain aggregates are actually Access functions as opposed to being part of VBA).

 

[Freezerbird]

Oh wow. There's a whole load of functions I've never seen before! So instead of using a sub-select to return the other team's score, it looks like I can just use DLookUp to do the same job? Light-bulbs are going on in my head....

That's magic, I'll give it a go! Cheers Leigh mate.

Dan

 

[LPurvis]

No worries.

There's an inherent overhead price to be paid when making function calls from queries - but that's the nature of the beast. As it was you were having to use a correlated subquery anyway - so it likely wouldn't have been screamingly fast had it even been permitted in a Jet db.

If the Domain functions are giving you all the performance you'd like, as I mentioned you can potentially drag a bit more by replacing them with your own custom functions making the data calls. It depends upon how things go.

Cheers.

 

[khawar]

Using VBA or Dlookups will be slower when working on large data
There is another option which is pretty efficient

With a non updatable query you cannot update the table but you can make a temporary table and then update your table from that temporary table exactly in the way you were going to do with query its many times faster than using vba or domain functions

Regards

 

[LPurvis]

>> "Using VBA or Dlookups will be slower when working on large data"
Yes I said as much. And indeed, when working on any data - but yes, more noticable on large datasets.

Naturally a temp table gives better performance - but is a move into a multi-step operation there.
It is trivial when the query is being executed in code (just an extra line beforehand to populate the local table before executing the main query).

FWIW in multi-step operations you can sometimes improve the lookup performance too with your own function which opens a recordset for the duration of the query call - and instead of an individual lookup performed with each row.
The inherent issue as in cases like this is the corrolated subquery though. The need for that will mean you can't create a particularly more efficient lookup function - and even your local table filling won't be massively expedient as it would be without it - though should still be somewhat faster.