INSERT 0..n records into table 'A' based on content of table 'B' in MySql 5
- by Robert Gowland
Using MySql 5, I have a task where I need to update one table based on the contents of another table.
For example, I need to add 'A1' to table 'A' if table 'B' contains 'B1'. I need to add 'A2a' and 'A2b' to table 'A' if table 'B' contains 'B2', etc.. In our case, the value in table 'B' we're interested is an enum.
Right now I have a stored procedure containing a series of statements like:
INSERT INTO A
SELECT 'A1'
FROM B
WHERE B.Value = 'B1';
--Repeat for 'B2' -> 'A2a'; 'B2' -> 'A2b'; 'B3' -> 'A3', etc...
Is there a nicer more DRY way of accomplishing this?
Edit:
There may be values in table 'B' that have no equivalent value for table 'A'.
