Solved

keywords count

Posted on 2014-01-29
6
392 Views
Last Modified: 2014-01-29
Dear all,
I have this table @KeyWords ( word, Count)
how to add all rows in @KeyWords  to another Table @AnotherKeyWords (word, Count)
if the word not exist in @AnotherKeyWords.


but if the word exist in @AnotherKeyWords   then @AnotherKeyWords .Count=@AnotherKeyWords .Count+@KeyWords.Count


thanks,
0
Comment
Question by:ethar1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
6 Comments
 
LVL 7

Assisted Solution

by:aplusexpert
aplusexpert earned 50 total points
ID: 39817388
Execute the following two queries:

Query1: add all rows in @KeyWords  to another Table @AnotherKeyWords (word, Count)
if the word not exist in @AnotherKeyWords:

INSERT INTO @AnotherKeyWords
FROM SELECT @KeyWords.Word, @KeyWords.COUNT FROM @KeyWords WHERE @KeyWords.Word NOT IN (SELECT @AnotherKeyWords.Word FROM @AnotherKeyWords)

Query2: if word exist in @AnotherKeyWords then @AnotherKeyWords .Count=@AnotherKeyWords .Count+@KeyWords.Count

UPDATE AKW SET AKW.COUNT = AKW.Count + @KeyWords.COUNT
FROM @AnotherKeyWords AKW INNER JOIN @KeyWORDS ON AKW.Word = @KeyWords.Word

(The inner join will ensure that only when Word is there in both tables, then COUNT will be updated).
0
 
LVL 12

Accepted Solution

by:
Harish Varghese earned 450 total points
ID: 39817397
Hello,
First do an update of the count in @AnotherKeyWords table for the matching words
And then insert the additional words from @KeyWords table along with count to @AnotherKeyWords.

Update AK
SET AK.Count = AK.Count + K.Count
From @AnotherKeyWords AK, @KeyWords K
Where AK.Word = K.Word

Insert into @AnotherKeyWords (Word, Count)
Select Word, Count
From @KeyWords K
Where Not Exists (Select 1 from @AnotherKeyWords AK Where AK.Word = K.Word)

-Harish
0
 

Author Closing Comment

by:ethar1
ID: 39817414
excellent
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question