Turning the same column from 3 rows into 3 columns in another table

There are three tables, table a, table b, and table c. Table b combines id's from the other two tables to define a 1-to-(1 to 3) relationship between a and c. So any a will have between 0 and 3 c's.

Performing joins and multiple selects to do validation on objects based on these relationships is too costly. I am trying to get rid of table b altogether, and define the relationships, in order of c.id, in table a.

What is the update query I'm supposed to run? I tried this:

UPDATE a SET c_a = (SELECT c_id from b WHERE a_id = a.id LIMIT 0,1 ORDER BY c_id asc);
UPDATE a SET c_b = (SELECT c_id from b WHERE a_id = a.id LIMIT 1,1 OR开发者_如何转开发DER BY c_id asc);
UPDATE a SET c_c = (SELECT c_id from b WHERE a_id = a.id LIMIT 2,1 ORDER BY c_id asc);

but that failed, because you cannot use LIMIT in a subquery in MySQL.

How do you do this in SQL?

This should work if you don't mind the order:

UPDATE a a, b b
   SET a.c_a = b.c_id
 WHERE a.id = b.a_id

UPDATE a a, b b
   SET a.c_b = b.c_id
 WHERE a.id = b.a_id
   and a.c_a <> b.c_id

UPDATE a a, b b
   SET a.c_c = b.c_id
 WHERE a.id = b.a_id
   and a.c_a <> b.c_id
   and a.c_b <> b.c_id