Merge/JOIN help please - 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 2000 Forums

Transact-SQL (2000)

Merge/JOIN help please

Author

Topic

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-12-10 : 12:29:10

Hi,

I would like to merge these tables but certain values need to be placed max data reached:

Data:
Table A - ID, NameID, DL (5 recs)
Table B - OrigName, Alias1, Alias2 (2 recs)

ID    NameID      DL            OrigName    Alias1        Alias2
1     999         DL100         Tony        TonyTiger     TTiger
2     999         DL200         Tony        TonyT         NULL
3     999         DL300         NULL        NULL          NULL
4     999         DL400         NULL        NULL          NULL
5     999         DL500         NULL        NULL          NULL

Hoping to get this:
ID    NameID      DL            OrigName    Alias1        Alias2
1     999         DL100         Tony        TonyTiger     TTiger
2     999         DL200         Tony        TonyT         NULL
3     999         DL300         Tony        TonyTiger     TTiger
4     999         DL400         Tony        TonyTiger     TTiger
5     999         DL500         Tony        TonyTiger     TTiger

So, update with "Tony        TonyTiger     TTiger" where OrigName is NULL values (ID = 3,4,5) which basically record 1 of Table B.

Thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 12:35:40

[code]UPDATE t1
SET t1.OrigName=t2.OrigName,
t1.Alias1 =t2.Alias1,
t1.Alias2=t2.Alias2
FROM table t1
JOIN table t2
ON t1.NameID=t2.NameID
AND t2.OrigName+t2.Alias1+t2.Alias2 IS NOT NULL
AND COALESCE(t1.OrigName,t1.Alias1,t1.Alias2) IS NULL[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources