pulling data from a different table to fill another

Forum Moderators: open

Message Too Old, No Replies

pulling data from a different table to fill another

sasori

7:51 pm on Jul 16, 2009 (gmt 0)

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

8:41 pm on Jul 16, 2009 (gmt 0)

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

11:37 pm on Jul 16, 2009 (gmt 0)

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

4:30 pm on Jul 21, 2009 (gmt 0)

I worked this out with a join