• Advertisement
Sign in to follow this  

[web] Help with a SQL Statement

This topic is 4474 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Hello, Basically what I have is 3 Database Tables, that all have a little information that needs to be inserted into a 4th database. I'm having some big troubles with the Statement..I need it something like this
INSERT INTO Table4 (Field1, Field2, Field3) VALUES 
(Table1.Field1 WHERE Table1.Field2 = 3)
(Table2.Field1 WHERE Table2.Field4 = 5)
(Table3.Field3 WHERE Table3.Field1 = 4)
Now thats not how i tried structring my SQL but I thought this way of writing it is more clear to see what i'm trying to do. I'm not really very good with SQL and can't figure out what way to get that to work, I tried nesting Select statments inside the insert statement, but those didn't seem to work(always errored after the WHERE clause) Any help would be appreciated. Thank You

Share this post


Link to post
Share on other sites
Advertisement
Well, that's one hell of a query, but I can tell you right off the bat, INSERT isn't used like that.

If you want to query 3 tables into a 4th, you need to use subqueries:

INSERT INTO table4 VALUES (
SELECT * FROM table3 WHERE (something matches)
);

Or do your queries in the right order in the software layer if the SQL is overwhelmingly complex.

Share this post


Link to post
Share on other sites
Might this work:

INSERT INTO Table4 (Field1, Field2, Field3) VALUES
((SELECT Field1 FROM Table1 WHERE Field2 = 3)
,(SELECT Field1 FROM Table2 WHERE Field4 = 5)
,(SELECT Field3 FROM Table3 WHERE Field1 = 4)
)


?

Share this post


Link to post
Share on other sites
No, you can't put selects in parallel like that. You have to formulate a correct join query first. Once you have that query, you can insert into the second table using an outer insert.

So, the initial query is something like:

select t1.field1 as field1_1,
t2.field1 as field2_1,
t3.field3 as field3_3
from table1 t1,
table2 t2,
table3 t3
where t1.field2 = 3
and t2.field4 = 5
and t3.field1 = 4;


This will create the correct join that extracts out the data you want. Chaining that into an insert would look something like:

insert into table4( fieldA, fieldB, fieldC )
select t1.field1 as field1_1,
t2.field1 as field2_1,
t3.field3 as field3_3
from table1 t1,
table2 t2,
table3 t3
where t1.field2 = 3
and t2.field4 = 5
and t3.field1 = 4;

Share this post


Link to post
Share on other sites
Quote:
Original post by hplus0603
No, you can't put selects in parallel like that. You have to formulate a correct join query first. Once you have that query, you can insert into the second table using an outer insert.

So, the initial query is something like:

select t1.field1 as field1_1,
t2.field1 as field2_1,
t3.field3 as field3_3
from table1 t1,
table2 t2,
table3 t3
where t1.field2 = 3
and t2.field4 = 5
and t3.field1 = 4;


This will create the correct join that extracts out the data you want. Chaining that into an insert would look something like:

insert into table4( fieldA, fieldB, fieldC )
select t1.field1 as field1_1,
t2.field1 as field2_1,
t3.field3 as field3_3
from table1 t1,
table2 t2,
table3 t3
where t1.field2 = 3
and t2.field4 = 5
and t3.field1 = 4;


Hehehe, I can't believe you actually wrote all of that out, especially seeing as he couldn't use it, and was just using it more as an example of what he needed to do.

I figured giving the subquery hint would get him close enough.. :p

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement