SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need Help on Insert Update
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqllover
Constraint Violating Yak Guru

India
331 Posts

Posted - 03/06/2014 :  20:03:44  Show Profile  Reply with Quote
hi,

Below is my sample data and table

	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


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


select 5 as idUserQues, 1 as userId,2 as idKey 

Am i doing anything wrong here please suggest me

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/07/2014 :  06:55:48  Show Profile  Reply with Quote
So whats your output? How do you determine which question is for which user?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sqllover
Constraint Violating Yak Guru

India
331 Posts

Posted - 03/07/2014 :  14:12:55  Show Profile  Reply with Quote
Hi Visakh,

Please execute the below query for the output as per my condition on the merge statement.


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  


If am not wrong that i am doing wrong on the condition what i included on the merge statement.

Any suggestion or modifications on the condition please help me on this.
Go to Top of Page

sqllover
Constraint Violating Yak Guru

India
331 Posts

Posted - 03/07/2014 :  18:27:16  Show Profile  Reply with Quote
This is how i achieved.


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 );
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000