how to use results of `select` as argument to `insert into`

Question

I'm hacking on my Zotero database with sqlite3, since the Zotero gui still doesn't support editing multiple records at once. Current task is finding all items from a particular journal, separating out the ones that don't yet have a value for journalAbbreviation, and adding the correct abbreviation. Here are the relevant tables:

fields (fieldID, fieldName, fieldFormatID)
itemData (itemID, fieldID, valueID)
itemDataValues (valueID, value)

Here is my query to find all the records I want:

SELECT itemData.itemID
FROM fields JOIN itemData JOIN itemDataValues 
WHERE (fields.fieldname IN ('publicationTitle'))  
AND (fields.fieldID = itemData.fieldID) 
AND (itemData.valueID = itemDataValues.valueID) 
AND (itemDataValues.value IN ('The Journal of the Acoustical Society of America'));

Now that I have that list of itemIDs, I want to add a bunch of entries to the itemData table. Schematically I want to do this:

INSERT INTO itemData (itemID, fieldID, valueID)
VALUES (A,X,Y),(B,X,Y),(C,X,Y), ... (W,X,Y);

where X is the fieldID for 'journalAbbreviation' and Y is the valueID for 'J. Acoust. Soc. Am.' (I know how to get those values). How can I write this INSERT INTO statement to replace A, B, C, etc with the itemIDs from my SELECT query above?

note: some of the records already have the journal abbreviation in there. I've only been writing SQL for a few days, so I don't know if it will cause problems to try to INSERT a record that already exists in the table (I'm assuming it will, based on a vague understanding of primary keys and the fact that the schema section in the itemData table includes a line PRIMARY KEY (itemID, fieldID)). So it may be necessary to first exclude those itemIDs that already have a record in itemData that includes X as fieldID.

...have you tried googling your problem? http://msdn.microsoft.com/en-us/library/ms188263%28v=sql.105%29.aspx http://stackoverflow.com/questions/25969/sql-insert-into-values-select-from this is pretty basic syntax as long as you know it's out there. — RandomUs1r, Mar 04 '13 at 18:38
@Syn123: I assumed it was out there, and I did google. As a novice SQL writer, I obviously didn't hit on the right search terms, or didn't recognize the right answer when I saw it. Everything I found seemed to imply that you had to write out each record of the `INSERT INTO ... VALUES(),(),();` statement separately, which seemed ludicrously inefficient. So I asked for help. — drammock, Mar 04 '13 at 18:50
If it helps, what I googled to find the above links was: "SQL INSERT INTO SELECT". I google most things related to SQL in a similar manner just because SQL is such a popular technology. — RandomUs1r, Mar 04 '13 at 19:49

Ravindra Gullapalli · Accepted Answer · 2013-03-04T19:49:26.997

1

With little discussion with @drammock, found a query that is working. Main part of this query is EXCEPT keyword.

INSERT INTO itemData (itemID, fieldID, valueID) 
SELECT itemData.itemID, 25, 8356
FROM fields JOIN itemData JOIN itemDataValues 
WHERE (fields.fieldname IN ('publicationTitle'))  
AND (fields.fieldID = itemData.fieldID) 
AND (itemData.valueID = itemDataValues.valueID) 
AND (itemDataValues.value IN ('The Journal of the Acoustical Society of America'))
EXCEPT SELECT itemData.itemID, 25, 8356
FROM fields JOIN itemData JOIN itemDataValues 
WHERE (fields.fieldname IN ('journalAbbreviation'))  
AND (fields.fieldID = itemData.fieldID) 
AND (itemData.valueID = itemDataValues.valueID) 
AND (itemDataValues.value IN ('J. Acoust. Soc. Am.', 'J Acoust Soc Am'));

edited Mar 04 '13 at 19:49

answered Mar 04 '13 at 18:39

Ravindra Gullapalli

9,049
3
48
70

use a `DISTINCT` keyword OR **INNER JOIN** to get different set of values. Check the updated query. – Ravindra Gullapalli Mar 04 '13 at 19:16
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/25550/discussion-between-ravindra-gullapalli-and-drammock) – Ravindra Gullapalli Mar 04 '13 at 19:28
explanatory note: 25 is the `fieldID` for "journalAbbreviation" and 8356 is the `valueID` for "J. Acoust. Soc. Am." – drammock Mar 04 '13 at 20:40

PeteGO · Answer 2 · 2013-03-04T19:30:17.367

This is basically a copy of Ravindra's answer only with DISTINCT and my preferred syntax of having the foreign key links in the FROM statement simplifying the WHERE clause, and in my opinion making it more readable.

I've also added a self join and said WHERE existing.itemID IS NULL to avoid inserting records that already exist.

INSERT INTO itemData (
  itemID, 
  fieldID, 
  valueID) 
SELECT DISTINCT
  itemData.itemID, 
  'journalAbbreviation', 
  'Journal of the Acoustical Society of America' 
FROM 
  fields 
    INNER JOIN itemData ON fields.fieldID = itemData.fieldID
    INNER JOIN itemDataValues ON itemData.valueID = itemDataValues.valueID
    LEFT OUTER JOIN itemData existing ON itemData.itemID = existing.itemID
WHERE 
  fields.fieldname = 'publicationTitle'
  AND itemDataValues.value = 'The Journal of the Acoustical Society of America'
  AND existing.itemID IS NULL

score 1 · Answer 3 · answered Mar 04 '13 at 21:19

1

You may want to do this via Zotero's javascript API, which makes data corruption a lot less likely. http://www.zotero.org/support/dev/client_coding/javascript_api

Also, if all you're looking for is journal abbreviations, consider running the branch xpi for Zotero's 3.1 branch, which has automatic journal abbreviations via a list from MEDLINE: http://www.zotero.org/support/dev_builds#zotero_31_branch

answered Mar 04 '13 at 21:19

adam.smith

808
4
9

I considered using the API, but I don't know Javascript very well. Good to know that abbreviations are on the way toward being automatically added though. – drammock Mar 04 '13 at 21:32

how to use results of `select` as argument to `insert into`

3 Answers3