0

I have 3 tables:

TABLE 1 userID | userName

TABLE 2 topicID | topicName

TABLE 3 userID | topicID (One user can have several topics asigned)

I am receiving a string of topicNames and a userID. I need to insert this data into table 3 but first I need to get the topicIds from table 2. Currently my solution would be to do a select query to get the topicIds for the topicNames (from table 2) and then use that to insert to Table 3.

Is there a way to do the insertion and the mapping in one query?

cesarcarlos
  • 1,271
  • 1
  • 13
  • 33
  • What do the strings look like? If it's a comma-separated list of `topicNames`, you can use `FIND_IN_SET()` to match this against `table2.topicName`. – Barmar Apr 07 '21 at 19:04

1 Answers1

0
@string := 'topicName,userID';  -- for example, 'my super-topic,123'

INSERT INTO table3 (userID, topicID)
SELECT SUBSTRING_INDEX(@string, ',', -1), topicID 
FROM table2
WHERE topicName = SUBSTRING_INDEX(@string, ',', 1);
Akina
  • 39,301
  • 5
  • 14
  • 25