pulling data from a different table to fill another

[sasori]

I would like to take this:
UPDATE `new_contact` SET province = "Texas" WHERE state = 43

and change 'Texas' to be states.name and state to be states.stid, where there is any match found between new_contact.state and states.stid.

Is this possible in a single query, or do I have to go to php?

Thanks!

[rocknbil]

create table states (id int(11), name varchar(50));
create table statestwo (id int(11), name varchar(50));
insert into states values(44,'Wyoming');
insert into states values (43, 'Texas');
insert into statestwo values (44, 'Blah Bla');
insert into statestwo values (43, '');

And here's your update:
update statestwo set name=(select name from states where states.id=statestwo.id);

select * from statestwo;

+---------+------------+ 
¦--id-----¦--name------¦ 
+---------+------------+ 
¦--44-----¦-Wyoming----¦ 
¦--43-----¦-Texas------¦ 
+---------+------------+ 

[sasori]

sorry, what I meant was that I already have the states table and its filled. It contains the data I want to place into new_contact, where appropriate.
So, entries of new_contact.state that contain 3 will get new_contact.province filled with California (which is found in states.stid and states.name, respectively.

[sasori]

I worked this out with a join