Not able to add multiple column against same ID in table-CodePudding

I have two tables x and y, x have the ID and many other columns, however y only have the ID similar as x table and then this ID is mapped to Many values

My Insert statement looks like this

INSERT INTO `table`
(`id`,
`other_name`)
VALUES
(select id from another_table where name = 'something'`,
('WALLETAB',
'SBTRADER',
'SBTRDACKING'));

expected result

1 | WALLETAB
1 | SBTRADER
1 | SBTRDACKING

I take ID from another table which already have data and this another table some different data associated with this table

CodePudding user response：

You could fetch id from another table to be used in insert statement by using limit 1, something like:

select id from another_table where name = 'something' limit 1

However, to insert all 3 rows you will need a multiple insert in a single statement.

insert into `table` values
  ((select id from another_table where name = 'something' limit 1), 'WALLETAB'),
  ((select id from another_table where name = 'something' limit 1), 'SBTRADER'),
  ((select id from another_table where name = 'something' limit 1), 'SBTRDACKING');

See fiddle: https://www.db-fiddle.com/f/gYvrxdsDxVQPkZM2o8YRT1/1

It feels a lot of duplication. You can simplify it by either using variable or CTE. The following query utilizes CTE which only usable on mysql 8 :

insert into `table` (id, other_name)
with
 other_id as (
   select id from another_table where name = 'something'),
 merged as (
   select id, other_name from other_id join 
   (select 'WALLETAB' as other_name
      union select 'SBTRADER'
      union select 'SBTRDACKING')
    as other_temp)
select * from merged;

The CTE above fetch the id on other_id. The union-select pairs is then used to create 3 rows containing 'WALLETAB', 'SBTRADER', and 'SBTRDACKING' respectively. Then both of them joined to get 3 rows with varying value on other_name but has id as 1.

See fiddle: https://www.db-fiddle.com/f/xgQta17bGphHAB81N2FNwX/1