1

I have a persistent table in SQLite (say PermTable). The schema for PermTable is

PermTable ( Node1 varchar(20), Node2 varchar(20), Node3 varchar(20), Frequency number, PRIMARY KEY (Node1, Node2, Node3))

I have a similar temporary table (say TempTable). TempTable has the same schema as PermTable.

For this question, let's assume the following content of PermTable and TempTable -

PermTable 

    Node1 | Node2 | Node3 | Frequency
    _________________________________
      A   |   B   |   C   |    2     
      B   |   D   |   E   |    3

TempTable

    Node1 | Node2 | Node3 | Frequency
    _________________________________
      A   |   B   |   C   |    6     
      D   |   E   |   C   |    4

I would like to update/copy values from TempTable into PermTable such that if any similar entry exists, the frequency is summed, else a plain insert takes place.

EXPECTED OUTPUT:

PermTable

 Node1 | Node2 | Node3 | Frequency
 _________________________________
   A   |   B   |   C   |    8     
   B   |   D   |   E   |    3
   D   |   E   |   C   |    4

I have looked into the links here, here or here. However, they either INSERT OR REPLACE some hard-coded values, or they use some external programming interface to get the status of query execution and do the subsequent steps.

I have to stick with SQLite and as such constructs like UPSERT, ON DUPLICATE KEY, MERGE, etc. is not favorable in this case.

Community
  • 1
  • 1
GvanJoic
  • 213
  • 2
  • 14
  • Check this [discussion](http://sqlite.1065341.n5.nabble.com/SQLite-equivalent-to-Oracle-s-MERGE-INTO-td67271.html) as well. It suggests writing 2 statements, one insert and one update. But it highlights the risk of data changing between these 2 transactions. Anyways worth reading. – Utsav Mar 24 '17 at 04:44
  • Why do you have to stick with SQLite only? Is this a homework assignment? – CL. Mar 24 '17 at 08:19

2 Answers2

1

First, update all rows that match (the ifnull results in zero if no match is found):

UPDATE PermTable
SET Frequency = Frequency +
        ifnull((SELECT Frequency
                FROM TempTable
                WHERE (TempTable.Node1, TempTable.Node2, TempTable.Node3) =
                      (PermTable.Node1, PermTable.Node2, PermTable.Node3)),
               0);

Then add all new rows:

INSERT INTO PermTable
SELECT * FROM TempTable
WHERE (Node1, Node2, Node3) NOT IN (SELECT Node1, Node2, Node3
                                    FROM PermTable);
CL.
  • 158,085
  • 15
  • 181
  • 214
-1

You could solve this problem using an UNION clause

DECLARE @PermTable TABLE ( Node1 varchar(20), Node2 varchar(20), Node3 varchar(20), Frequency int)
DECLARE @TempTable TABLE ( Node1 varchar(20), Node2 varchar(20), Node3 varchar(20), Frequency int)

INSERT INTO @PermTable VALUES
('A','B','C',2),
('B','D','E',3)

INSERT INTO @TempTable VALUES
('A','B','C',6),
('D','E','C',4)

SELECT a.Node1, a.Node2, a.Node3, SUM(a.Frequency) AS Frequency
FROM (
  SELECT  p.Node1, p.Node2, p.Node3, p.Frequency
  FROM  @PermTable AS p
  UNION ALL
  SELECT  t.Node1, t.Node2, t.Node3, t.Frequency
  FROM  @TempTable AS t) AS a
GROUP BY a.Node1, a.Node2, a.Node3
Kevin
  • 751
  • 5
  • 11