CODEWITHSUNDEEP
mysql
Emma
Emma

Reputation: 65

Mysql INSERT data in Table 2 for each row in Table 1

I have two tables.

Table A is a list of questions that needs to be filtered by question type. e.g. SELECT * from TableA WHERE Qtype = "whatever"

I then need to add four new records to Table B for each record pulled by the query on TableA.

Table A and Table B join on an ID number - TableA.id = TableB.questionid

I am presuming I will need to run it four times, once for each record that I add.

I am getting confused on the FOR EACH command in conjunction with an INNER JOIN

Would appreciate any help!

Upvotes: 0

Views: 3498

Answers (2)

Peter Lang
Peter Lang

Reputation: 55624

I'm afraid I still don't fully understand your question, but this will insert three records into TableB for each record of TableA, with three different values for newfield:

INSERT INTO TableB (ID, newfield) (
  SELECT a.ID, n.newfield
  FROM (
    SELECT 'Excellent' AS newfield UNION ALL
    SELECT 'Something' UNION ALL
    SELECT 'Something else'
  ) n
  CROSS JOIN TableA a
)

Upvotes: 3

adrien
adrien

Reputation: 4439

Try this 4 times :

INSERT INTO TableB (questionid, otherfieldB)
VALUES (
    SELECT id, otherfieldA
    FROM TableA
    WHERE Qtype = "whatever"
)

Upvotes: 3

Related Questions