I have a problem with a query and an insert.
I have a database that maybe isn't the best design - it includes a field "Former Names" which is varchar 255.
It contains a list of names separated by commas. e.g.: "Svea Jarl, Apollo III "
I would like to
a) strip off the quote marks;
b) Insert the whole row into a new row;
c) Assign the id number (auto increment field called idCruise);
d) Have the first 'former name' become the 'Name' field;
e) Have the original 'Name' field either go into former names or into an "also known as" field (which exists);
f) Then repeat b through e for each former name;
Is my best bet to create a new table, indexing on idCruise and containing one field for each former name and then running a select / insert query based on the contents of that table?
?maybe renaming the Name field to "Current Name"?
How do I avoid an endless loop where the new rows don't get checked then the rows that are inserted from that check get checked ..... ad nauseum?
Thanks for any help anyone can offer.