CODEWITHSUNDEEP
sqlselectsqlitebulkinsert
drammock
drammock

Reputation: 2543

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

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.

Upvotes: 1

Views: 155

Answers (3)

adam.smith
adam.smith

Reputation: 928

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

Upvotes: 1

Ravindra Gullapalli
Ravindra Gullapalli

Reputation: 9158

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

Upvotes: 1

PeteGO
PeteGO

Reputation: 5791

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

Upvotes: 1

Related Questions