Must use updatable query

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

I've had this before, and its annoying - anyone know why
I get this error message? Here's the SQL:
UPDATE Temp_Deploy_Status SET
Temp_Deploy_Status.Account_ID = (SELECT tmp_acct.Acc_ID
FROM Temp_Deploy_Status, tmp_acct;)
WHERE (((Temp_Deploy_Status.Account_ID) Is Null));


tia
tim
 
Hi, Tim -

Well, the first thing that jumps out is that the 2 tables in your
subquery aren't joined in any way. But I have to question whether you
even need a subquery at all. Assuming I understand what you're trying to
do, I'd suggest:

UPDATE Temp_Deploy_Status RIGHT JOIN tmp_acct ON
Temp_Deploy_Status.Account_ID = tmp_acct.Acc_id
SET Temp_Deploy_Status.Account_ID = tmp_acct.Acc_ID
WHERE Temp_Deploy_Status.Account_ID IS NULL;

hth,

LeAnne
 
When you use a Sub-Query in an Update Query (or an updateable Select Query),
JET needs the SubQuery to be updateable Query also.

In your case, the SubQuery is a Cartesian-join Query which is certainly not
updateable.

Another thing to consider is to ensure that the SubQuery returns only 1 row
x 1 column dataset, i.e. single-value dataset in your case. However, your
Cartesian-join without any criteria will return mn values where m is the
number of Records in the first Table and n is the number of Records in the
second Table.
 
Back
Top