CODEWITHSUNDEEP
mysql
Linjhara
Linjhara

Reputation: 37

update table if not present then insert

this question is in continuation with my previous question.

join 2 tables and save into third-sql

this time, situation is something like this

TableA
+-------------------+--------+
| wordA(primarykey) | countA |
+-------------------+--------+
| pqr               |      5 |
| abcd              |     20 |
| pqrs              |     45 |
+-------------------+--------+

TableB
+-------------------+--------+
| wordB(primarykey) | countB |
+-------------------+--------+
| pq                |     10 |
| abc               |     10 |
| pqrs              |     10 |
| abcdef            |      5 |
+-------------------+--------+

This time TableC already have these tuples in it. If any word which is already present in TableC, then its count will be incremented by the value of TableA or TableB. if not then that new word is add into it. It is something like we are updating the table and inserting if update is not done.

TableC(before query)
+--------+--------+--------+
|  word  | countA | countB |
+--------+--------+--------+
| ab     |      0 |     10 |
| abc    |     25 |     40 |
| abcd   |     29 |      0 |
| abcde  |     45 |     90 |
| abcdef |      0 |     55 |
+--------+--------+--------+

Desired output:
TableC(after query)
+--------+--------+--------+
|  word  | countA | countB |
+--------+--------+--------+
| ab     |      0 |     10 |
| abc    |     25 |     50 |
| abcd   |     49 |      0 |
| abcde  |     45 |     90 |
| abcdef |      0 |     60 |
| pq     |      0 |     10 |
| pqr    |      5 |      0 |
| pqrs   |     45 |     10 |
+--------+--------+--------+

Upvotes: 1

Views: 63

Answers (2)

valex
valex

Reputation: 24144

In MySQL you can do it in one query if TableC.Word is a PRIMARY KEY using ON DUPLICATE KEY syntax:

SQLFiddle demo

INSERT INTO TableC(Word,CountA,CountB)

SELECT * FROM
(
SELECT Word,SUM(CountA) as Ca,SUM(CountB) as Cb 
FROM
(
  SELECT WordA as Word,CountA, 0 as CountB FROM TableA
   UNION ALL
  SELECT WordB as Word,0 as CountA, CountB FROM TableB
) T GROUP BY Word  
) T1  

ON DUPLICATE KEY 
UPDATE CountA = CountA+T1.Ca,CountB = CountB+T1.Cb

Upvotes: 3

vijaykumar
vijaykumar

Reputation: 4806

Use ON DUPLICATE KEY UPDATE Try this may help you. 

INSERT INTO 'your previos query'
ON DUPLICATE KEY UPDATE 
  countA = coutnA +1

Upvotes: 0

Related Questions