Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – INSERT INTO SELECT each row with different external value

I have a query which is working properly:

INSERT INTO t1 (COL_1, COL_2, COL_3) 
SELECT COL_1, 'external value', COL_3 
FROM t2 
WHERE COL_4 IN ('1', '2', '3', '4')

So, ‘external value’ goes to each row in t1.
The question is: How can I add different ‘external value’ to rows? For example, the first row has ‘ext val 1’, the second row has ‘ext val 2’ and so on…

Is there any solution to this approach?

THX.

Currently, I have no any idea for INSERT INTO SELECT, but if possible I do not want to switch to INSERT INTO VALUES … Additional info, the target DB is MYSQL.

Tags:

2

Answers


  1. 0 
    INSERT INTO t1 (COL_1, COL_2, COL_3) 
SELECT COL_1, concat('external value',' ',COL_4), COL_3 
FROM t2 
WHERE COL_4 IN ('1', '2', '3', '4');
    Login or Signup to reply.
  2. 0 
    INSERT INTO t1 (COL_1, COL_2, COL_3) 
SELECT t2.COL_1, external_values.external_value, t2.COL_3 
FROM t2 
JOIN ( SELECT 1, 'value 1' UNION ALL
       SELECT 2, 'value 2' UNION ALL
       SELECT 3, 'value 3' UNION ALL
       SELECT 4, 'value 4'  
       ) AS external_values (col_4, external_value) USING (col_4)

    fiddle

    The values can be provided in any other rowset form – for example, as an output of JSON_TABLE function.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search