Access INSERT with nested SELECT

Question

Why does the following SQL statement not work?

INSERT INTO dialog (speaker, dialog_text) VALUES (
    (
        SELECT FIRST(id)
        FROM FIGURE
        WHERE char_name="Doe" AND forename="John"
    ),
    "Some text"
);

It produces this error:

Query input must contain at least one table or query.

The single SELECT statement works.

score 2 · Answer 1 · answered Jan 26 '16 at 19:15

2

Following works:

   INSERT INTO dialog (speaker, dialog_text) 
            SELECT FIRST(id), "Some text"
            FROM FIGURE
            WHERE char_name="Doe" AND forename="John"

answered Jan 26 '16 at 19:15

Piotr Siekierski

484
2
7

Would it work with a second `SELECT` statement? I could not figure out how. – Sandro Koch Jan 26 '16 at 21:59

score 2 · Accepted Answer · edited May 23 '17 at 12:30

2

An Access SQL INSERT ... VALUES statement will not let you use a subquery for one of the VALUES

Switching to an INSERT ... SELECT statement, as Piotr suggested will work.

Or you could use an Access Domain Aggregate function, instead of a subquery, in your INSERT ... VALUES statement:

INSERT INTO dialog (speaker, dialog_text)
VALUES (
    DMin("id", "FIGURE", "char_name='Doe' AND forename='John'"),
    'Some text'
);

edited May 23 '17 at 12:30

Community

1
1

answered Jan 26 '16 at 19:28

HansUp

95,961
11
77
135

When i run this code in MS Access everthing works fine, but when i try to run it in C#, parametrized, i get a "To few parameters." exception. Do you know why? Shall i open a new thread for this question? – Sandro Koch Jan 26 '16 at 21:55
I think we would need to see the parameter query and the c# code which supplies the parameter values. So, yes, that is different enough that it should be a new question. – HansUp Jan 26 '16 at 22:03

Access INSERT with nested SELECT

2 Answers2

Linked