jet sql update statement 1

[muneco]

Hi, I am new to creating db statements in Access 2000 and would appreciate your feedback.

I have three tables:

ORIG_SHIPS
id
vessel type

MVMT_TYPE
mvmt_type_id
mvmt_type_abbrev

MOVEMENT
mvmt_id
mvmt_type_id

In my database, movement.mvmt_type_id is currently null in all rows.

I would like to update the value of movement.mvmt_type_id
with the value of mvmt_type.mvmt_type_id,
where movement.mvmt_id = orig_ships.id
and orig_ships.vessel_type = mvmt_type.mvmt_type_abbrev.

I have tried and tried several SQL statements without success. I may be way off base, but my latest attempt reads like this...

update movement set mvmt_type_id = (select mvmt_type.mvmt_type_id from orig_ships, mvmt_type
where orig_ships.vessel_type = mvmt_type.mvmt_type_abbrev);

I get an error message
"operation must use an updatable query".

Can you suggest a better approach than the update statement I have written?

 

[swampBoogie]

Access has some quirks and it does not allow for subqueries in update is one.

You need to use a syntax like

update a,b set a.c = b.d where a.pk = b.pk

What is the relation between movement and mvmt_type? I don't see that orig_ships is necessary in this context.

 

[KenReay]

Hi

Are you saying there is a relationship between vessel_type and mvmt_type_abbrev
??
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[muneco]

swampBoogie,

Your advice worked great, I would never have tried anything like that, coming from an Informix, Oracle, and DB2 background. Thanks so much for sharing your expertise, you definitely get a star from me for this one.

Here is the statement that worked properly as per your advice:

UPDATE movement, mvmt_type, orig_ships SET movement.mvmt_type_id = mvmt_type.mvmt_type_id
WHERE movement.mvmt_id = orig_ships.id
and orig_ships.vessel_type = mvmt_type.mvmt_type_abbrev;

Thanks also to KenReay for your reply, sorry if my description was not clear/complete.