0

I have two tables: BT and ST. Here is what I hope I can accomplish:

INSERT INTO BT (c1,c2,c3) values (v1,v2,v3)

ST is a table with 89 rows and one column. Can and if so, how can I formulate a query so that the insert works for every one of the 89 values from the ST table?

(Bear in mind: the values are random and hold no mathematical connection by which I could tie them in)

c1-c3= column1,column2,column3

v1-v3= value1,value2,value3 (v2 and v3 are predefined and set and do not require alteration)

I tried the more obvious things without success, such as:

INSERT INTO BT (c1,c2,c3) values ((select c1 from ST),v2,v3)

But all that line does is return a general sql syntax error.

I acknowledge this can be performed with 89 rows of "INSERT INTO" and me changing that one value every time, but can this be done in a more elegant way?

I'm using MySQL

Nikola Pavlovic
  • 91
  • 10

1 Answers1

3

You just want insert . . . select:

insert into BT (c1, c2, c3) 
   select c1, v2, v3
   from ST
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • But ST holds only the values for v1, whereas the v2 and v3 are set once in the query. (/* SQL Error (1241): Operand should contain 1 column(s) */) is the result from that suggestion – Nikola Pavlovic Jul 04 '18 at 22:02
  • @NikolaPavlovic . . . I don't understand. You can set variables `@v1` and `@v2` or just pass in constants. – Gordon Linoff Jul 04 '18 at 22:42
  • I want to use SQL to execute insert into query into BT table with v2 and v3 values set by me, but v1 to be used from the c1 column of the ST table – Nikola Pavlovic Jul 05 '18 at 06:28
  • This works actually, but v2 and v3 have to be defined as additional columns because BT has 3 columns and ST has only one> insert into BT (c1,c2,c3) select c1,'number1' as v2,'number2' as v3 from ST – Nikola Pavlovic Jul 05 '18 at 08:19
  • @NikolaPavlovic . . . Yes, you can just pass constants in. – Gordon Linoff Jul 06 '18 at 01:34