🔒 Locked

[web] Help with a SQL Statement

Started by ArchG Jan 20, 2006 at 2:01 PM 3 replies 400+ views

Original Post

Jan 20, 2006 at 2:01 PM

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

ciroknight

Jan 20, 2006 at 2:06 PM

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.

RDragon1

206

RDragon1

Jan 20, 2006 at 2:08 PM

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)
)

?

hplus0603

Mod

3,014

hplus0603

Jan 20, 2006 at 2:17 PM

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;

ciroknight

Jan 20, 2006 at 2:27 PM

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

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.