Multiple insertion, with 1 default value!

rubino

Hi to everyone, I need to make a multiple insertion in MySQL and I wrote the SQL string as follows:

INSERT INTO tableX (field1, field2, field3)
SELECT fieldA, fieldB, fieldC FROM tableY WHERE fieldA = 'xyz'

but myproblem is that one of the fields should have always the same value, obtained from a $GET, something like:

INSERT INTO tabellaX (field1, field2, field3)
VALUES ($GET['value'], SELECT fieldB, fieldC FROM tableY WHERE fieldA= 'xyz')

obviously can't be done this way, but...how then?!

Thanks in advance!

jheitz

That's exactly what you wan't to do. Why can't you????

rubino

u mean I can write a query like the second one? (i know the first one is correct!)

I just want to insert multiple records, 2 fields taken fron a SELECT (from another table) and the 3rd field always the same for each insertion, equal to a GET['value']...

are u saying my 2nd query

INSERT INTO tabellaX (field1, field2, field3)
VALUES ($GET['value'], SELECT fieldB, fieldC FROM tableY WHERE fieldA= 'xyz')

could work?!

rubino

ok, I tried this way:

INSERT INTO tabellaX (field1, field2, field3)
VALUES ($GET['value'], (SELECT fieldB, fieldC FROM tableY WHERE fieldA= 'xyz'))

it gives this error:

Subquery returns more than 1 row

As if expecting just 1 row and not multiple

How can I manage this!

rubino

wowwwwwwwwww looks like I did it...and I wanna share happiness & knowledge!

Should be written this way:

INSERT INTO tabellaX (field1, field2, field3)
SELECT '" . $GET['value'] . "', fieldB, fieldC FROM tableY WHERE fieldA= 'xyz'))

in this way the first value is static, and the other fields are imported from table two...

anyone surprised?!...I'll go out and drink a lot, have fun!!! (thanks to u all)

jheitz

Exactly, that is what I meant, I had misread your original INSERT. You can combine columns and static values (such as getdate() or now() depending on the RDBMS) on the select statement. Glad you figured it out yourself