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
--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)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments