Ok, let me explain my logic and please correct me if I'm wrong... I gather from my limited understanding of MySQL and rdbms that it is not advisable to store multiple values in one field on a db (such as the color values in Table-A below). I was thinking that I needed to have a separate table for these color values, then do a lookup using the ID field to retrieve the colors when needed. The color field on Table-A would not be needed after creating and populating Table-B. Table-A will contain around 1,000 records.
Am I off track here? What would be the correct way to design this db? Thanks for the help. Brent -----Original Message----- From: Adam Fortuno KOVICK [mailto:[EMAIL PROTECTED] Sent: Friday, August 01, 2003 2:13 PM To: [EMAIL PROTECTED] Cc: MySQL List Subject: Re: RE: Newbie question...memo field Brent, Maybe but give us more information. I know you moving data from one table into a new one, but not much else. Whats the logic to the changes you're making? A$ ----- Original Message ----- From: Brent Elison <[EMAIL PROTECTED]> Date: Friday, August 1, 2003 3:25 pm Subject: RE: Newbie question...memo field > I appreciate the suggestion. However, this solution will require > me to run > step (3) for each record I want updated into Table-B. That would > be 1000+ > times. > > Any other way to get this done without having to run all those > updates? > Thanks, > > Brent > -----Original Message----- > From: Adam Fortuno KOVICK [EMAIL PROTECTED] > Sent: Friday, August 01, 2003 11:31 AM > To: [EMAIL PROTECTED] > Cc: MySQL List > Subject: Re: Newbie question...memo field > > > Normally I'd say do a select...into...from, but I don't think MySQL > supports that. In lew of that, try this: > > (1) Create the new table. > > CREATE TABLE tbl_table_b ( > record SMALLINT NULL, > id SMALLINT NULL, > color VARCHAR(10) > ); > > (2) Insert the values from the source table to the destination. > > INSERT INTO tbl_b SELECT record, id, color FROM tbl_a; > > > (3) Update the values you want changed. > > UPDATE tbl_b SET record = 2 id = 001 WHERE color = 'Pink'; > <Continue for each additional row to be updated> > > Not sure if this is what you were after. > > Regards, > A$ > > ----- Original Message ----- > From: Brent Elison <[EMAIL PROTECTED]> > Date: Friday, August 1, 2003 11:19 am > Subject: Newbie question...memo field > > > Hi all, > > > > I'm fairly new to MySQL and have the following scenario: > > > > The Table-A below was converted from a Filemaker Pro database. > > The color > > field was a value list in the FMP database that converted over as > > a text > > field. The color field contains all the selected values in the > > value list > > (from the FMP database) and looks like the data below (Table-A) > > when viewed > > in the BLOB-editor (MySQL-Front). So, how do I extract the color > > valuesfrom the color field in Table-A and put them into a separate > > table looking > > like Table-B? > > > > Sorry for the totally newbie question. Thanks for any suggestion. > > > > Brent > > > > > > Table-A > > Record ID Color > > ------------------------ > > 1 001 Black > > Pink > > White > > 2 002 Black > > 3 003 Green > > 4 AAA Black > > White > > ------------------------ > > > > > > Table-B > > Record ID Color > > ------------------------ > > 1 001 Black > > 2 001 Pink > > 3 001 White > > 4 002 Black > > 5 003 Green > > 6 AAA Black > > 7 AAA White > > ----------------------------- > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
