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.
Printable View
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?
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)
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';