Multicolumn correlated 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 2012 Forums

Transact-SQL (2012)

Multicolumn correlated update

Author

Topic

SMerrill
Posting Yak Master

206 Posts

Posted - 2013-05-03 : 14:23:05

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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-05-03 : 15:16:43

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

UnemployedInOz
Yak Posting Veteran

54 Posts

Posted - 2013-05-03 : 21:08:50

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-05-04 : 12:58:24

[code]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';[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources