UPDATE with LEFT-JOIN (conditional update query) (2 Viewers)

AOB

Registered User.
Local time
Today, 23:46
Joined
Sep 26, 2012
Messages
637
I am trying to UPDATE a table based on the result/value for the corresponding record in a separate query.

This is the basic syntax of the query :

SQL:
PARAMETERS SpecifiedRecordID Long;

UPDATE tblRecords R

LEFT JOIN qryOutstanding AS Q ON R.RecordID = Q.RecordID

SET R.LockID = SWITCH(Nz(Q.Outstanding,0)>0,-1,TRUE,0)

WHERE R.RecordID = SpecifiedRecordID;

So basically, look for the ID in the query, if the corresponding value for the record in question is 0, or the record is not present at all, set the LockID to 0, otherwise (i.e. there is a corresponding value for the record and it is greater than 0), set the LockID to -1.

Access doesn't like it as "Operation must use an updateable query"

How can I write this kind of UPDATE query where the update is conditional on the output of a separate LEFT-JOIN'd query?

Thanks!
 
You could remove the qryOutstanding stuff and use a domain aggregate function like dLookup().
 
What is qryOutstanding SQL?

Why do you UPDATE Outstanding field? I suspect this value should just be calculated when needed and not saved to table.
 
What is qryOutstanding SQL?

Why do you UPDATE Outstanding field? I suspect this value should just be calculated when needed and not saved to table.
I don't update the Outstanding field. I update a field specific to the tblRecords table based on the number of outstanding items against it elsewhere. qryOutstanding is just an aggregate SELECT query that gives me a calculation of all outstanding items per record.
 
You could remove the qryOutstanding stuff and use a domain aggregate function like dLookup().
I could but I figured a simple JOIN on an existing (fast) query would be a lot easier? Evidently not?
 
Apology for my error.

Why do you UPDATE LockID field?

Perhaps sample data would be helpful.

Access does not allow UPDATE when join involves non-editable dataset.
 
You could remove the qryOutstanding stuff and use a domain aggregate function like dLookup().
So, I mean... This works :

SQL:
PARAMETERS SpecifiedRecordID Long;

UPDATE tblRecords R

SET R.LockID = SWITCH(Nz(DLookup("Outstanding","[qryOutstanding]","[RecordID] = " & SpecifiedRecordID),0)>0,-1,TRUE,0)

WHERE R.RecordID = SpecifiedRecordID;

But it seems awfully clunky compared to a "straight" SQL solution?
 
The point is why have tblRecords.LockID at all?

Couldn't you just have a simple query like:
SQL:
PARAMETERS SpecifiedRecordID Long;
SELECT
  R.*,
  IIf(Nz(Q.Outstanding,0)>0, -1, 0) AS LockID
FROM tblRecords R
LEFT JOIN qryOutstanding Q
       ON R.RecordID = Q.RecordID
WHERE R.RecordID = SpecifiedRecordID;
 
You can sometimes make the query updateable by specifying unique values (DISTINCTROW)

Code:
PARAMETERS SpecifiedRecordID Long;
UPDATE DISTINCTROW tblRecords R
LEFT JOIN qryOutstanding AS Q ON R.RecordID = Q.RecordID
SET R.LockID = SWITCH(Nz(Q.Outstanding,0)>0,-1,TRUE,0)
WHERE R.RecordID = SpecifiedRecordID;
 

Users who are viewing this thread

Back
Top Bottom