Using SQL MERGE INTO without the need of support table

Question

Using a preparedStatement in Java (under DB2) we want to control the LAST access time of each user to the app.

So, we have a table named 'users_access' with two fields: 'user_id' and 'access_date'.

Does somebody know if it is possible to use SQL 'merge into' sentence to insert the record if it is the first time the user connects; or update the record (in concrete the date) in successive loggings.

This works only if the record yet exists:

 MERGE INTO users_access a USING 
   (SELECT user_id FROM users_access WHERE user_id = ?) b 
 ON (a.user_id = b.user_id)                                   
 WHEN MATCHED THEN           
 UPDATE SET access_date = ? 
 WHEN NOT MATCHED THEN     
 INSERT (                  
     user_id, access_date 
 ) VALUES (                
     ?, ?                  
 )

Perhaps [this](http://stackoverflow.com/questions/330241/ddg#330364) might help? — g00se, Jun 09 '22 at 12:30
The problem is that source_table is the same as target_table — JLLMNCHR, Jun 09 '22 at 12:46
So did you try [this](https://stackoverflow.com/a/23784606/16376827)? — g00se, Jun 09 '22 at 12:58

score 2 · Accepted Answer · answered Jun 09 '22 at 13:03

Try this:

MERGE INTO USERS_ACCESS A
USING (VALUES (CAST (? AS VARCHAR (128)), CAST (? AS TIMESTAMP))) B (USER_ID, ACCESS_DATE) 
  ON B.USER_ID = A.USER_ID 
WHEN MATCHED THEN UPDATE SET ACCESS_DATE = B.ACCESS_DATE
WHEN NOT MATCHED THEN INSERT (USER_ID, ACCESS_DATE) VALUES (B.USER_ID, B.ACCESS_DATE)

score 0 · Answer 2 · answered Jun 09 '22 at 13:05

0

You can use an if exists like this

if exists(Select 1 from users_access WHERE user_id = @user_id) 
  update users_access  set access_date = @access_date 
else insert into users_access select @user_id, @access_date

answered Jun 09 '22 at 13:05

Alexandru Petre

23
1
6

Using SQL MERGE INTO without the need of support table

2 Answers2