0

I want to do something like

insert into table ( A, B, C
) (select '1', one.A, one.B from table_1 one) UNION (select '2', one.A, one.B from table_1 one)

Will this call select query twice ? Are there any better way of doing this ?

Peng
  • 1
  • 2
  • yes, it runs select twice.. how could it do anything other than that? you're selecting from two different tables. a join wouldn't be possible, because that'd produce a single `(a,b)`-type record set for the insert, rather than separate `(a),(b)` – Marc B Oct 12 '16 at 21:07
  • @MarcB, Sorry that I didn't write it correct. I am selecting from the same table. What I want to do is : For every row I selected, I want to insert two rows into another table – Peng Oct 12 '16 at 21:09
  • you can't produce rows out of nothing in mysql. if you want two rows inserted, you have to produce two rows, which means running `select` twice. – Marc B Oct 12 '16 at 21:12
  • moral of the story is: `UNION ALL` is your friend – Drew Oct 12 '16 at 21:14
  • Frankly though, I would consider keeping union out of the whole thing. Just do 2 insert stmts – Drew Oct 12 '16 at 21:18
  • Another way to return the set is a join operation, between table_1 and an inline view that returns two rows... **`SELECT d.i, o.a, o.b FROM (SELECT '1' AS i UNION ALL SELECT '2') d CROSS JOIN table_1 o`** (Question is closed, so this can't be left as an answer. Personally, I'd prefer a single SELECT statement to the UNION/UNION ALL operation. I don't know if this can objectively be considered "a better way" or not.) – spencer7593 Oct 12 '16 at 21:46

0 Answers0