I don't want to make a multiple record insert, but to insert one row in the table.
With the nested SELECT query I want to get an Id from another table.
I don't want to make a multiple record insert, but to insert one row in the table.
With the nested SELECT query I want to get an Id from another table.
Did you read the link I posted? The multiple record syntax can insert one or more records.
If you have to insert values from another table as well as literal values you will have to get creative:
or maybe:Code:INSERT INTO SomeTable (PersonID, FavouriteFood, Location) SELECT ID, 'Fried Carrots', 'Jail' FROM People WHERE Name='some name';
Code:INSERT INTO SomeTable (PersonID, FavouriteFood, Location) SELECT ID, "Fried Carrots" AS FavouriteFood, "Jail" AS Location FROM People WHERE Name='some name';
Sorry, I took a look, but I obviously missed this. Now I understand. Thank you.
This question could be a little bit sily, but in the documentation
doesn't say anything about:
What about if I want to run SELECT query on two or more
tables to get Id's?
Last edited by Gravedigga; 08-01-2004 at 11:18 AM.
Well, you should be able to use the normal table joining syntax (INNER JOIN, simple WHERE join, etc). How do you need to combine the two tables?
OK. I have three tables:
1)Reasons with Id AUTOINCREMENT and Reason TEXT
2)People with Id AUTOINCREMENT, names TEXT, address TEXT
3)Orders with Id AUTOINCREMENT,PersonId INT, ReasonId INT, Value FLOAT and OrderDate Date
I want to INSERT a record in Orders like this:
This query works with Postgres SQL, but not with Access throughtCode:INSERT INTO Orders(PersonID,ReasonID,Value,OrderDate) VALUES((SELECT Id FROM People WHERE Names='some name'), (SELECT Id FROM Reasons WHERE Reason='some reason'),1.50, '1/2/2004');
ODBC. I guess that nested queries are not allowed and do it with
separate queries, but is there any way with one SQL statement?
And I need LEFT/RIGHT JOIN to query the Orders table after that.
(To get the Names throught PersonId, for example)
Last edited by Gravedigga; 08-01-2004 at 03:04 PM.
You can probably just use a table combine. When you don't provide a join condition jet combines all possible combinations. You can then add the where clause to only get the row you are interested in.
For example if you have table 1 with data items '1a', '1b', '1c' and table 2 with data items '2a' '2b' jet creates from:
something like this:Code:Select table1.col1, table2.col1 FROM table1, table2
All the possible combinations are created! If you don't use a WHERE clause you could, in theory, get a massive result set. A two column result set created from two tables with 10000 records each would give you a hundred million records.Code:1a 2a 1a 2b 1b 2a 1b 2b 1c 2a 1c 2b
Anyway, with this behaviour in mind, you would use something like this:
Code:INSERT INTO Orders(PersonID, ReasonID, Value, OrderDate) SELECT People.Id, Reasons.Id, 1.5, '1/2/2004' FROM People, Reasons WHERE People.Names='some name' AND Reasons.Reason='some reason';
