INSERT 0..n records into table 'A' based on content of table 'B' in MySql 5

Question

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'.

Edit: Example

Given Table B

+-------+
| Value |
+-------+
| B1    |
| B1    |
| B2    |
| B3    |
| FOO   |
+-------+

Expect Table A

+-------+
| Value |
+-------+
| A1    |
| A2a   |
| A2b   |
| A3    |
+-------+

Answer 1

Use a CASE statement:

INSERT INTO TABLE_A
SELECT CASE 
         WHEN b.value = 'B1' THEN 'A1'
         WHEN b.value = 'B2' THEN 'A2a'
       END
  FROM TABLE_B b

I need to add 'A2a' and 'A2b' to table 'A' if table 'B' contains 'B2'

---

Sorry, one value can be returned. A switch statement allows fall through cases, but wouldn't return more than one value either.

Answer 2

You could consider creating a table that contains the test/result combinations you want, eg:

TABLE Tests

Test  |  Result
----------------
B1    |  A1
B2    |  A2a
B2    |  A2b
B3    |  A3

Then you can inner join this table to TABLE_B and read out the resulting Result column to determine the values to insert into TABLE_A:

INSERT INTO TABLE_A
SELECT DISTINCT TABLE_B.Result
FROM TESTS, TABLE_B
WHERE TABLE_B.Value = TESTS.Test