select inside update?

[Paul]

Hi,
I'm trying to UPDATE a field according to a criteria I SELECT from
another table. I thought I could do that in one statement - but it
gives me an error. I'm doing it from Java. What am I doing wrong?

The update statement:
UPDATE Accounts SET StatusID = (SELECT AcctStatusID FROM AcctStatus
WHERE AcctStatusName = 'Suspended') WHERE AcctID = 192

It produces:
[Microsoft][ODBC Microsoft Access Driver] Operation must use an
updateable query.

Obviously, when I do just
SELECT AcctStatusID FROM AcctStatus WHERE AcctStatusName = 'Suspended'

it gives me the right ID. And if I do

UPDATE Accounts SET StatusID = 4 WHERE AcctID = 192

it also works fine. But not as one statement.

[Jason Lepack]

UPDATE Accounts, AcctStatus SET StatusID = [AcctStatusID]
WHERE AcctID=192 AND AcctStatusName='Suspended';

Cheers,
Jason Lepack

On Jan 27, 2:05 pm, "Paul" <[e-mail]:

Hi,
I'm trying to UPDATE a field according to a criteria I SELECT from
another table. I thought I could do that in one statement - but it
gives me an error. I'm doing it from Java. What am I doing wrong?

The update statement:
UPDATE Accounts SET StatusID = (SELECT AcctStatusID FROM AcctStatus
WHERE AcctStatusName = 'Suspended') WHERE AcctID = 192

It produces:
[Microsoft][ODBC Microsoft Access Driver] Operation must use an
updateable query.

Obviously, when I do just
SELECT AcctStatusID FROM AcctStatus WHERE AcctStatusName = 'Suspended'

it gives me the right ID. And if I do

UPDATE Accounts SET StatusID = 4 WHERE AcctID = 192

it also works fine. But not as one statement.

[Paul]

Thanks a lot Jason, works perfectly. I didn't know you could include
tables you weren't actually updating in an UPDATE statement.
Paul

On Jan 27, 3:48 pm, "Jason Lepack" <[e-mail]:

UPDATE Accounts, AcctStatus SET StatusID = [AcctStatusID]
WHERE AcctID=192 AND AcctStatusName='Suspended';

Cheers,
Jason Lepack

On Jan 27, 2:05 pm, "Paul" <[e-mail]:

Hi,
I'm trying to UPDATE a field according to a criteria I SELECT from
another table. I thought I could do that in one statement - but it
gives me an error. I'm doing it from Java. What am I doing wrong?

The update statement:
UPDATE Accounts SET StatusID = (SELECT AcctStatusID FROM AcctStatus
WHERE AcctStatusName = 'Suspended') WHERE AcctID = 192

It produces:
[Microsoft][ODBC Microsoft Access Driver] Operation must use an
updateable query.

Obviously, when I do just
SELECT AcctStatusID FROM AcctStatus WHERE AcctStatusName = 'Suspended'

it gives me the right ID. And if I do

UPDATE Accounts SET StatusID = 4 WHERE AcctID = 192

it also works fine. But not as one statement.