Updating history based on current table

Question

Tables

We have two tables History (Hist) and Current (Curr). see pic for details I have to update Hist on a daily basis based on Curr that records in real time when a user listens to a given song. Basically, at the end of each day, we go to Curr and pull a count of each user/song combination and then add this count to Hist that has the lifetime count. If it is the first time a user has listened to a given song, we won't have this pair in Hist, so we have to create the pair there and then add the count of the last day.

Below is the code that I am trying, and a few questions:

Does it look ok, or am I making an error?
Do I need to write the query again in the IS NOT NULL condition as I am doing currently
Is there a better and more efficient way of doing this?

Select case 
when IS NULL  
(
    Select User_ID, Song
    From History H, 
        (Select User_ID, Song, count (*) as song_cnt
        From Current C
        Group by User_ID, Song) as X
        Where X.User_ID = H.User_ID 
        AND X.Song=H.Song 
) 
Then
    (Update History Set Count = Count +X.Song_cnt, where History.User_ID = User_ID AND History.Song = Song)
When IS NOT NULL  
(
    Select User_ID, Song
    From History H, 
        (Select User_ID, Song, count (*) as song_cnt
        From Current C
        Group by User_ID, Song) as X
        Where X.User_ID = H.User_ID 
        AND X.Song=H.Song
) 
Then
(Insert into History values (todays_date, User_ID, Song, X.Song_cnt)
End;

Is this Oracle or Postgres? Those are two different products. If it's Postgres you can use an [upsert stataement](https://stackoverflow.com/questions/17267417/how-to-upsert-merge-insert-on-duplicate-update-in-postgresql). If it's oracle you can a [merge statement](https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606) — JNevill, Jan 08 '18 at 18:58
Tag properly!!!! It's either Oracle or Postgres, can't be both!!! — Eric, Jan 08 '18 at 19:06
Please read http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557 and the accepted answer — , Jan 08 '18 at 20:33

score 0 · Accepted Answer · answered Jan 08 '18 at 19:07

I think a MERGE INTO statement like this should be all that you need, if you are using Oracle.

MERGE INTO history H
USING (SELECT user_id,
              song,
              Count (*) AS song_cnt
       FROM   CURRENT C
       GROUP  BY user_id,
                 song) c
ON ( c.user_id = h.user_id AND c.song=h.song )
WHEN matched THEN
  UPDATE SET h.song_cnt = h.song_cnt + c.song_cnt
WHEN NOT matched THEN
  INSERT (todays_date,
          user_id,
          song,
          song_cnt)
  VALUES (SYSDATE,  -- or your date variable
          c.user_id,
          c.song,
          c.song_cnt)  ;

Updating history based on current table

1 Answers1