March 6, 2014 at 1:47 pm
hi,
Below is my sample data and table
with Users as (
select 1 asuserid,'name1' as Name union all
select 2 asuserid,'name2' as Name union all
select 3 asuserid,'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
March 7, 2014 at 10:34 am
The Merge is doing exactly what you are asking. It is inserting whenever there isn't a match. You need to limit the rows for the insert. So your merge needs to be something like this:
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 );
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 7, 2014 at 12:36 pm
Hi Jack,
Thank you Gentle Man. It worked perfectly. I am not aware of i can add AND condition with WHEN Statement. New learning today. Way to go. Great Tip and Help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply