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 2012 Forums
 Transact-SQL (2012)
 Multicolumn correlated update
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SMerrill
Posting Yak Master

USA
206 Posts

Posted - 05/03/2013 :  14:23:05  Show Profile  Click to see SMerrill's MSN Messenger address  Reply with Quote
Team,
I'm having trouble which ever way I go...
I've tried correlated updates, I've tried Merge, I don't know anymore.

Please help me update tblIndividual.CustomerTkn with tbl_CIS_Partner.New_Partner and
TBLINDIVIDUAL.PREMISE_TKN with TBL_CMS_DBSYNC.CN_CSPM01_PREMLOC_TK.

Original Select statement:
SELECT IND.CUSTOMERTKN,
  P.NEW_PARTNER,
  IND.PREMISE_TKN,
  Sync.CN_CSPM01_PREMLOC_TK
FROM TBLINDIVIDUAL IND
INNER JOIN TBL_CIS_PARTNER P
ON IND.CUSTTKN_PRE_SAS = P.OLD_PARTNER
AND IND.CUSTOMERTKN   <> P.NEW_PARTNER
INNER JOIN TBL_CMS_DBSYNC Sync
ON P.NEW_PARTNER         = Sync.CN_CLIENT_ID_NBR
WHERE IND.CURRENT_ACTIVE = '1'

Correlated Update:

MERGE INTO TBLINDIVIDUAL IND USING
(SELECT P.NEW_PARTNER,
  Sync.CN_CSPM01_PREMLOC_TK
FROM TBL_CIS_PARTNER P
INNER JOIN TBL_CMS_DBSYNC SYNC
ON P.NEW_PARTNER          = SYNC.CN_CLIENT_ID_NBR --AND IND.CUSTOMERTKN <> P.NEW_PARTNER and IND.CUSTTKN_PRE_SAS = P.OLD_PARTNER
) ON ( IND.CURRENT_ACTIVE = '1' --AND IND.CUSTOMERTKN <> P.NEW_PARTNER and IND.CUSTTKN_PRE_SAS = P.OLD_PARTNER
)
WHEN MATCHED THEN
  UPDATE
  SET CUSTOMERTKN  =P.NEW_PARTNER,
    IND.PREMISE_TKN=SYNC.CN_CSPM01_PREMLOC_TK ;


~ Shaun Merrill
Seattle area

Lamprey
Flowing Fount of Yak Knowledge

4347 Posts

Posted - 05/03/2013 :  15:16:43  Show Profile  Reply with Quote
Would it be correct to assume the select works but the merge does not? If you can explain what you want to do in words, we might be able to help you better. Or, even better would be posting your DDL, DML and expected output so we can run queries against your actual data.

Here are some links to help you provide that:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

UnemployedInOz
Yak Posting Veteran

Australia
54 Posts

Posted - 05/03/2013 :  21:08:50  Show Profile  Reply with Quote
Update tblIndividual set
CustomerTkn = New_Partner,
PREMISE_TKN = CN_CSPM01_PREMLOC_TK
FROM TBL_CIS_PARTNER P
ON tblIndividual.CUSTTKN_PRE_SAS = P.OLD_PARTNER
JOIN TBL_CMS_DBSYNC Sync
ON P.NEW_PARTNER = Sync.CN_CLIENT_ID_NBR
WHERE tblIndividual.CURRENT_ACTIVE = '1' AND tblIndividual.CUSTOMERTKN <> P.NEW_PARTNER
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 05/04/2013 :  12:58:24  Show Profile  Visit SwePeso's Homepage  Reply with Quote
UPDATE		IND
SET		IND.CUSTOMERTKN = P.NEW_PARTNER,
		IND.PREMISE_TKN = Sync.CN_CSPM01_PREMLOC_TK
FROM		dbo.TBLINDIVIDUAL AS IND
INNER JOIN	dbo.TBL_CIS_PARTNER AS P ON P.OLD_PARTNER = IND.CUSTTKN_PRE_SAS
			AND P.NEW_PARTNER <> IND.CUSTOMERTKN
INNER JOIN	dbo.TBL_CMS_DBSYNC AS Sync ON Sync.CN_CLIENT_ID_NBR = P.NEW_PARTNER
WHERE		IND.CURRENT_ACTIVE = '1';



N 56°04'39.26"
E 12°55'05.63"
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.06 seconds. Powered By: Snitz Forums 2000