Search code examples
sqldistinctinsert-select

Insert-Select for single result of multiple rows in same table


I do not know if title of this question makes any sense, but please allow me to explain my problem.

I have table like this.

DocTag table

DocId | TagId
   10 | 8
   10 | 45
   11 | 2
   11 | 15
   12 | 9
   12 | 32
   13 | 8
   13 | 15

Tag table

TagId | TagName
    8 | HomePage
    2 | Private
   45 | IssuerNameOne
   15 | IssuerNameTwo
   32 | IssuerNameThree
    9 | TagThatNeedsToBeSkipped
 3000 | NewTag

DocTag table contains FK's from Document and Tag tables. Now i have to select those documents that have tags with ids 8 or 2 and one of other ids (eg: 45, 32, 15), and when i find that Document in this table i have to insert [DocId | 3000], where 3000 is id of new tag.

In other words i have to select documents that belongs HomePage or Private and one of mentioned issuers and assign new tag to that document.

I have millions of documents and hundreds of tags, and 72 different issuers, so i suppose i have to execute query 72 times for every issuer.

For "IssuerNameOne" result of selection query should be:

DocId
   10       

because of 8 and 45 TagIds.

For "IssuerNameTwo" result of selection query should be:

DocId
   11
   13

because of 2, 8 and 15 TagIds.

After insert execution, DocTag should looks like this:

DocId | TagId
   10 | 8
   10 | 45
   11 | 2
   11 | 15
   12 | 9
   12 | 32
   13 | 8
   13 | 15
   10 | 3000
   11 | 3000
   13 | 3000

Solution

  • --INSERT INTO DocTag  (DocId,TagId)
    SELECT DISTINCT DocId, 3000
    FROM DocTag  t1 
    WHERE TagId IN(8,2)
    -- Check the DocId also has a TagId `IN(45,32,15)`
    AND EXISTS (SELECT 1 FROM DocTag  t2 
                WHERE t2.DocId=t1.DocId AND t2.TagId IN(45,32,15))
    -- Check the new tag mapping doesn't already exists
    AND NOT EXISTS(SELECT 1 FROM DocTag  t3 
                   WHERE t3.DocId=t1.DocId AND t3.TagId=3000)
    

    Fiddle