[RESOLVED] update query question

[Desbrina]

I have 2 tables

memberlist

CREATE TABLE IF NOT EXISTS `memberlist` (
  `ID` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `level` varchar(12) NOT NULL,
  `collecting` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `birthday` varchar(255) NOT NULL,
  `date_joined` date NOT NULL,
  `prejoiner` tinyint(1) NOT NULL,
  `pending` tinyint(1) NOT NULL default '1',
  `hiatus` tinyint(1) NOT NULL default '0',
  `credits` int(11) NOT NULL default '50',
  `lastlogin` date NOT NULL,
  `session` varchar(255) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=95 ;

and trades_completed

CREATE TABLE IF NOT EXISTS `trade_completed` (
  `ID` int(11) NOT NULL auto_increment,
  `sender` text NOT NULL,
  `reciever` text NOT NULL,
  `trade1` text NOT NULL,
  `trade2` text NOT NULL,
  `trade3` text NOT NULL,
  `trade4` text NOT NULL,
  `trade5` text NOT NULL,
  `recieve1` text NOT NULL,
  `recieve2` text NOT NULL,
  `recieve3` text NOT NULL,
  `recieve4` text NOT NULL,
  `recieve5` text NOT NULL,
  `date` date NOT NULL,
  `month` varchar(255) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1767 ;

Currently the sender/reciever is stored as text. What i've been trying to do (with no sucess) is convert the names to the correct id. The sender/reciever in the trades table is the same as the name field in the memberlist table. The memberlist ID is what i want to put in the trades table.

Can anyone help me out?

[djjjozsi]

ALTER TABLE trade_completed ADD s_id INT NOT NULL ,
ADD r_id INT NOT NULL ;

and this query could update these new two fields:

UPDATE trade_completed
SET trade_completed.s_id=(SELECT memberlist.ID FROM memberlist WHERE memberlist.name=trade_completed.sender LIMIT 1),
trade_completed.r_id=(SELECT memberlist.ID FROM memberlist WHERE memberlist.name=trade_completed.reciever LIMIT 1)

[Desbrina]

thanks, then i just did

 UPDATE trade_completed SET `sender` = `s_id` ,
`reciever` = `r_id` 

to update the original fields, didn't think of doing it that way

[djjjozsi]

the problem will comes when you want to select those fields in one query,
if you Join these tables, these field types are not in the same,
that might cause you a little trouble.
that update query put the member's ID into s_id and r_id...
its the cleanest way.

[Desbrina]

if you mean the sender/reciever fields aren't the same as the ID in the memberlist then thats sorted. Once i had the IDs i changed the two field types to int