4

SQL Question

I wonder if I can write a single sql INSERT statement to insert a row into a table where some of the fields come from variables and others come from another table. Currently I select from tableA, store the fields in variables and then insert them into tableB. In the following example I'd like to get field1, field2, and field3 from tableA and the rest from variables. (mysql)

$sql = "SELECT field1, field2, field3 FROM tableA where product = 5";

php code

$sql = "INSERT tableB
SET     
fkey = 100,
custid = 10,
product = 5,
field1 = '$field1',
field2 = '$field2',
field3 = '$field3' ";
zkanoca
  • 9,664
  • 9
  • 50
  • 94
sdfor
  • 6,324
  • 13
  • 51
  • 61
  • Please check this SO posting http://stackoverflow.com/questions/74162/how-to-do-insert-into-a-table-records-extracted-from-another-table. I think it answers your question. – mjv Sep 23 '09 at 21:59
  • thanks. my question must not be clear. I am trying to get field1 etc from tableA in the same tableB INSERT statement. – sdfor Sep 23 '09 at 22:06

2 Answers2

7

Yes, you just mix literal values and fields in a select and insert:

insert into tableB (fkey, custid, product, field1, field2, field3)
select 100, 10, 5, field1, field2, field3
from tableA
where product = 5
Guffa
  • 687,336
  • 108
  • 737
  • 1,005
2
INSERT INTO tableB (fkey, custid, product, field1, field2, field3)
SELECT 100, 10, product, field1, field2, field3 FROM tableA
 WHERE product = 5

You can, of course, replace constants in above statement with your variables.

ChssPly76
  • 99,456
  • 24
  • 206
  • 195