CODEWITHSUNDEEP
sqlms-accessms-access-2007sql-insert
ayca altay
ayca altay

Reputation: 51

SQL MsAccess embedding select into INSERT statement for some specific values

I have two tables: Artist and Work.

Artist is a relation of painters with ArtistID, FirstName and LastName.

Work is a relation of their paintings with columns WorkID, Title and ArtistID.

Now, my artist relation has data and I am now inserting values to Work table. WorkID is a counter, so no problems there. I want to use something that does

INSERT INTO Work (Title, ArtistID)
VALUES('Toledo', SELECT ArtistID FROM Artist WHERE FirstName='Joan');

But it doesn't seem to work or accept the Select Statement as a part of insert. What should I do?

Upvotes: 4

Views: 4903

Answers (3)

Gord Thompson
Gord Thompson

Reputation: 123399

This is an interesting question because both of the answers from Apostolos and from HansUp will work. However, they will behave differently if you have more than one [Artist] named 'Joan'.

In that case, Apostolos' method will insert multiple rows into [Work], while the approach taken by HansUp will only insert one row into [Work] based on the [ArtistID] returned by DLookup() (which will be the first match that it finds in the [Artist] table).

You'll have to decide which answer suits your purposes best. Be sure to Accept that answer, but also bear in mind that you can still upvote any answers that you find helpful.

Upvotes: 2

HansUp
HansUp

Reputation: 97101

Use a DLookup Function to retrieve the ArtistID value you want to insert. Notice the similarity between this DLookup expression and your SELECT statement ...

INSERT INTO Work (Title, ArtistID)
VALUES('Toledo', DLookup("ArtistID", "Artist", "FirstName='Joan'"));

Upvotes: 1

Apostolos
Apostolos

Reputation: 10463

Try this

INSERT INTO Work (Title, ArtistID) 
SELECT 'Toledo', ArtistID FROM Artist WHERE FirstName='Joan'

Upvotes: 5

Related Questions