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

Question

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

score 1 · Accepted Answer · answered Nov 23 '13 at 10:58

1

--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

answered Nov 23 '13 at 10:58

T I

9,785
4
29
51

Thank you for your help. It looks like a working solution, unfortunately i can not test it until Monday, so i'll post you back later. Thanks again. – bajicdusko Nov 23 '13 at 11:07
Although i gave up from inserting new tags solution, this is correct answer. @Mark answer is almost identical. This one is acceped because of cleaner syntax. Thank you. – bajicdusko Nov 26 '13 at 08:11

score 1 · Answer 2 · answered Nov 23 '13 at 11:03

1

insert into DocTag  
select d.docId, 3000 from DocTag d
join DocTag d1 on d1.docId = d.docId and d1.tagId in (45, 32, 15)
 where d.tagId in (8, 2)
and not exists (select * from DocTag where docId = d.docId);

answered Nov 23 '13 at 11:03

MartenCatcher

2,713
8
26
39

Thank you Mark. Basically it is the same solution as @TI submitted, so i suppose it is 100% percent working solution. I can not test it until Monday, so i'll let you know what happened :). – bajicdusko Nov 23 '13 at 11:16

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

2 Answers2