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
Need Help on Insert Update
Posted - 03/06/2014 : 20:03:44
