Is it possible to insert values from a select-union statement?

[Guest_imported]

i was trying to insert values from the two select-left-join statements by merging them with the use of "union"..... but i wasn't able to do so... there was an DAO error of missing operator... can you help me? If you have some other suggestions on how could i merge values from two select-left-join statements into 1 table, i'd be happy to take them into consideration.... Thanks in advance....

 

[swilliams]

What code were you trying to use that gave you the error??

Can you post it here??

Simon [sig]<p>Simon<br>[/sig]

 

[Guest_imported]

Thank you simon for replying.... anyway, here's the code...

select temp.stud_no as stud_no, guard.g_rel as relation, guard.g_assist as g_assis, guard.g_add as g_add from temp left join guard on temp.stud_no = guard.stud_no
UNION select temp.stud_no as stud_no, idendata.spouse as spouse, idendata.numchild asnumchild, idendata.ages as ages from temp left join idendata on temp.stud_no =idendata.stud_no;

lets say stud_no = 01

obviously this statement will result to two records of 01... one from the table of guard and the second is from the idendata, what if i want these two records be emerged into 1 record?

if i'll add an additional insert statement on the statement above (to try whether it's possible to put the result values from the state-join-union i have to the another table which is temp2 table), as expected, i have encountered a syntax error(missing operator) in query statement....

here's the statement:
insert into temp2
select temp.stud_no as stud_no, guard.g_rel as relation, guard.g_assist as g_assis, guard.g_add as g_add from temp left join guard on temp.stud_no = guard.stud_no
UNION select temp.stud_no as stud_no, idendata.spouse as spouse, idendata.numchild asnumchild, idendata.ages as ages from temp left join idendata on temp.stud_no =idendata.stud_no;

mmmmmmm.... so what's supposed to be the problem?

thanks again....

janice

 

[swilliams]

Firstly, in the union statement you have ..., idendata.numchild asnumchild,... but this should be idendata.numchild as numchild,... (space missing).
Secondly, whenever I do an insert query I always list the fields that I am inserting into, even if I am inserting into all fields in the table, so your insert statement would be:
INSERT INTO temp2 (field1, field2, ...)
SELECT ....
Thirdly, SELECT ... UNION SELECT ... on its own may not return two records if the second record is identical to teh first. If you want two records in this case, you should use SELECT ... UNION ALL SELECT ...

Hope this helps.

Simon [sig]<p>Simon<br>[/sig]

 

[Guest_imported]

Thank you once again Simon for giving me advice..... i really do appreciate your kindess! Anyway, im here once again to clarify and ask somethings

First and foremost, im quite aware of the fact that there should be space between &quot;as&quot; and numchild... im sorry about the typographical error...

about the insert statement... i added additional code which u have suggested....

insert into temp2 (stud_no, g-rel, g_assist, g_add, spouse, ages, numchild)
select temp.stud_no as stud_no, guard.g_rel as g_re, guard.g_assist as g_assis, guard.g_add as g_add from temp left join guard on temp.stud_no = guard.stud_no
UNION select temp.stud_no as stud_no, idendata.spouse as spouse, idendata.numchild as numchild, idendata.ages as ages from temp left join idendata on temp.stud_no =idendata.stud_no;

and i have encountered another error with this one.... &quot;No. of query values and destination fileds aren't the same&quot; This is probably bec. the two table consist of different fields... MMMM.... whatelse am i goin' to do?

and believe it or not.... the statement i posted here before do result into two records of student number 01... perhaps the fields from the guard and idendata tables are different.

I'm sorry if i ask too much, but i really need some help.... Hope to hear from u soon... Thanks!

janice

 

[RayMan]

Janice,
first run your select and make sure the results from it fit the table to which you are inserting. If the columns do not match then maybe you can use a view (Oracle, Sysbase, etc) to make the columns in the select union match the table to which the columns are being inserted.

Ray