Need Help on Insert Update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need Help on Insert Update

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-03-06 : 20:03:44

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

52326 Posts

Posted - 2014-03-07 : 06:55:48

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

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-03-07 : 14:12:55

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.

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-03-07 : 18:27:16

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 );

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources