Using SQL MERGE INTO without the need of support table-CodePudding

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 (                
     ?, ?                  
 )

CodePudding user response：

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)

CodePudding user response：

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