SQL MsAccess embedding select into INSERT statement for some specific values

Question

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?

Apostolos · Accepted Answer · 2013-10-24 12:06:57Z

5

Try this

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

answered Oct 24, 2013 at 12:06

Apostolos

10.5k5 gold badges32 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gord Thompson · Accepted Answer · 2013-10-24 12:42:47Z

2

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.

answered Oct 24, 2013 at 12:42

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

Comments

HansUp · Accepted Answer · 2013-10-24 12:17:03Z

1

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'"));

answered Oct 24, 2013 at 12:17

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Collectives™ on Stack Overflow

SQL MsAccess embedding select into INSERT statement for some specific values

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related