Help with UPDATE statement

[ladygray]

Hello,
I am new to Oracle and SQL. I am having trouble using an update statement to fill in a column in a table. I am working with the following tables:

table name: mtest
spcode
sptype
plotnum
sampyear

table name: a3a_species
species_co
species_ty
sapling
is_tree

I would like to update the column sptype with the values that are in species_ty where the values in spcode are equal to those in species_co. I have tried the following script:

update mtest
set sptype=a3a_species.species_ty where spcode=a3a_species.species_co;

I get the following error message: SQL Error: ORA-00904: "A3A_SPECIES"."SPECIES_CO": invalid identifier
00904. 00000 - "%s: invalid identifier"

I am really stuck with this. Any advice would be much appreciated. Thanks in advance,

- Ladygray

[skhanal]

you are missing reference to a3a_species table.

update mtest
set sptype=(select species_ty from a3a_species where spcode=species_co)

[ladygray]

Thank you SO much. That is fantastic.