INSERT INTO ... SELECT Subquery

[TheDeceived]

Hey all. I'm back again for some of your wisdom



I'm trying to create a copy and paste function in my database using the INSERT INTO and SELECT subquery. Basically, the user should be able to copy an item and all of it's children to another parent group.

The query which I have got so far is (pseudo code):

INSERT INTO Table1 (Table1.Column1, Table1.Column2, Table1.Column3)
VALUES (ManualValue1, ManualValue2, SELECT Table1.Column3 FROM Table1 WHERE Table1.Column1 = Condition1 AND Table1.Column2 = Condition2

What this should be doing is inserting two manual values into Column1 and Column2 of Table1, and then copying the value in Column3 of the same table where Column1 and Column2 are equal to a condition.

It will probably help if I put it into an example. If I we name the table "CustomerNames", and the columns FirstName, MiddleName and Surname. Imagine one record already exists within this table:

FirstName | MiddleName | Surname
"John" | "Adam" | "Smith"

Now what I want to do is add another record in there where I can manually set the FirstName and MiddleName fields, but copy the Surname field. Based on the pseudo code I wrote above, I'd write the SQL statement as:

INSERT INTO CustomerNames (CustomerNames.FirstName, CustomerNames.MiddleName, CustomerNames.Surname)
VALUES ('Paul', 'Mark', SELECT CustomerNames.Surname FROM CustomerNames WHERE CustomerNames.FirstName = 'John' AND CustomerNames.MiddleName = 'Adam'

So the table would now look like this:

FirstName | MiddleName | Surname
"John" | "Adam" | "Smith"
"Paul" | "Mark" | "Smith"

However, I keep getting an error from Access claiming that CustomerName.Surname does not exist.

After spending hours looking through Google and this forum, I cannot find an answer as to how to properly setup an INSERT INTO ... SELECT SQL statement where you have manual + copied values being entered into a table.

Could anyone point out where I'm going wrong here?

[weekend00]

INSERT INTO CustomerNames (CustomerNames.FirstName, CustomerNames.MiddleName, CustomerNames.Surname)
SELECT 'Paul', 'Mark', CustomerNames.Surname FROM CustomerNames WHERE CustomerNames.FirstName = 'John' AND CustomerNames.MiddleName = 'Adam'