We have a table that contains articles. In the table, there is a field that specifies which section a particular article belongs to, however, it is NOT a foreign key to another table, but is just a text field (bad, I know, I didn't design the D😎.
My question is, is there a query I can run that would automatically replace the section field in the article table with the id of a section from the section table?
This is what the article table is currently defined as (MySQL table). I've taken out fields that are not pertainant to this question:
CREATE TABLE story_table (
headline text,
subhead text,
summary text,
section text NOT NULL,
id int(11) unsigned NOT NULL default '0',
PRIMARY KEY (id),
UNIQUE KEY id (id)
) TYPE=ISAM;
This is the proposed section table:
create table section (
id int unsigned NOT NULL AUTO_INCREMENT,
db_name tinytext,
display_name tinytext,
description tinytext,
order int unsigned default '0',
PRIMARY KEY (id)
);
