Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2011
    Posts
    27

    Unanswered:

    Hi,

    Below is my sample data and table

    Code:
    with Users as (						
    select 1 as	userid,'name1' as Name union all
    select 2 as	userid,'name2' as Name union all		
    select 3 as	userid,'name3' as Name	)						
    							
    					
    with Keys as (
    select 1 as idKey, 'Question1' as Quesion union all
    select 2 as idKey, 'Question2' as Quesion union all
    select 3 as idKey, 'Question3' as Quesion union all
    select 4 as idKey, 'Question4' as Quesion union all
    select 5 as idKey, 'Question5' as Quesion union all
    select 6 as idKey, 'Question6' as Quesion )
    
    with User_Questions	 as (
    
    select 1 as idUserQues, 1 as userId,1 as idKey  union all
    select 2 as idUserQues,2 as userId, 2 as idKey  union all
    select 3 as idUserQues,2 as userId, 3 as idKey  union all
    select 4 as idUserQues,3 as userId, 3 as idKey)
    The logic i am trying is i want to insert/update into User_Questions for few users.

    for example i want to check the question belongs to Iduser and if it's available in User_questions then do some update operation. if not insert the data for the iduser.

    [Note : i want to check with Question on the merge condition]

    many times i used the Merge statement. Below is my merge statement for my case

    Code:
    MERGE User_Questions AS UQ
    				USING Keys K on(UQ.idKey = K.idKey and 
    				K.Quesion like'Question2%' and UQ.userId = 1)
    				
    				WHEN MATCHED
    					THEN
    						UPDATE
    						
    				WHEN NOT MATCHED
    					THEN
    						Insert
    On my experience i haven't used more than one condition in ON. but the above merge statement has three conditions which i must use. But the query is not working fine.

    It inserts all the questions of Keys table into user_questions table which should not be because as per the condition used it should insert only one record
    as below

    the following record has to be inserted into user_questions table for the condition i used on merge

    Code:
    select 5 as idUserQues, 1 as userId,2 as idKey
    Am i doing anything wrong here please suggest me

    Hi,

    Below is my sample data and table

    Code:
    with Users as (						
    select 1 as	userid,'name1' as Name union all
    select 2 as	userid,'name2' as Name union all		
    select 3 as	userid,'name3' as Name	)						
    						
    					
    with Keys as (
    select 1 as idKey, 'Question1' as Quesion union all
    select 2 as idKey, 'Question2' as Quesion union all
    select 3 as idKey, 'Question3' as Quesion union all
    select 4 as idKey, 'Question4' as Quesion union all
    select 5 as idKey, 'Question5' as Quesion union all
    select 6 as idKey, 'Question6' as Quesion )
    
    with User_Questions	 as (
    
    select 1 as idUserQues, 1 as userId,1 as idKey  union all
    select 2 as idUserQues,2 as userId, 2 as idKey  union all
    select 3 as idUserQues,2 as userId, 3 as idKey  union all
    select 4 as idUserQues,3 as userId, 3 as idKey)
    The logic i am trying is i want to insert/update into User_Questions for few users.

    for example i want to check the question belongs to Iduser and if it's available in User_questions then do some update operation. if not insert the data for the iduser.

    [Note : i want to check with Question on the merge condition]

    many times i used the Merge statement. Below is my merge statement for my case

    Code:
    	
    
    MERGE User_Questions AS UQ
    				USING Keys K on(UQ.idKey = K.idKey and 
    				K.Quesion like'Question2%' and UQ.userId = 1)
    				
    				WHEN MATCHED
    					THEN
    						UPDATE
    						
    				WHEN NOT MATCHED
    					THEN
    						Insert
    On my experience i haven't used more than one condition in ON. but the above merge statement has three conditions which i must use. But the query is not working fine.

    It inserts all the questions of Keys table into user_questions table which should not be because as per the condition used it should insert only one record
    as below

    the following record has to be inserted into user_questions table for the condition i used on merge

    Code:
    	
    select 5 as idUserQues, 1 as userId,2 as idKey
    After the merge with my condition below are the result

    Code:
    select 1 as idUserQues, 1 as userId,1 as idKey  union all
    select 2 as idUserQues,2 as userId, 2 as idKey  union all
    select 3 as idUserQues,2 as userId, 3 as idKey  union all
    select 4 as idUserQues,3 as userId, 3 as idKey
    select 5 as idUserQues, 1 as userId,2 as idKey
    Am i doing anything wrong here please suggest me
    Last edited by Pat Phelan; 03-10-14 at 11:29.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What do you want to do, if MATCHED?

    For example
    Code:
    MERGE User_Questions AS UQ
    				USING Keys K on(UQ.idKey = K.idKey and 
    				K.Quesion like'Question2%' and UQ.userId = 2)
    				
    				WHEN MATCHED
    					THEN
    						UPDATE
    						 SET   ...

  3. #3
    Join Date
    Apr 2011
    Posts
    27
    Hi Tonkuma,
    Thanks for your time. This how i achieved.

    Code:
    MERGE User_Questions AS UQ
    USING Keys K
    ON ( UQ.idKey = K.idKey
         AND K.Quesion LIKE 'Question2%'
         AND UQ.userId = 1
       )
    WHEN MATCHED THEN
        UPDATE SET userid = 1 ,
                   idKey = K.idkey
    WHEN NOT MATCHED AND K.quesion LIKE 'Question2%' THEN
        INSERT ( userID, idKey )
        VALUES ( 1, K.idKey );

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Having done statistics and surveys for a living, I would not do this in SQL (surprised? Hey, I often use other tools). You can get tools that will do a better job and be ready when they come out of the box. I liked Raosoft (Raosoft, Inc. - Company Information), but there are others.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •