CODEWITHSUNDEEP
sqlsql-server
Jimson James
Jimson James

Reputation: 3317

Update column based on multiple table inner join

I have a few tables as shown below, where OrigMachine.ID and OrigKeys.Id are same.

OrigMachine: ID, Name
OrigKeys :ID, Key

BackupMachine : ID,Name
BackupKeys : ID, Key

For example, My OrigMachine table is as shown below.

ID, Name
1,  Alfa
2,  Beta

My BackupMachine table is as shown below.

ID, Name
1,  Beta
2,  Gamma

My OrigKeys table is as shown below.

ID, Name
1,  AlfaParticle
2,  Beta1particle

My backupKeys table is as shown below.

ID, Name
1,  BetaParticle
2,  GammaParticle

After the update, the expected OrigKeys table is as shown below.

ID, Name
1,  AlfaParticle
2,  BetaParticle

Need to update OrigKeys.Key with BackupKeys.Key, based on OrigMachine.Name = BackupMachine.Name

This is what i have came up with, but not seems to work.

UPDATE [DB].[dbo].[OrigKeys]
SET [DB].[dbo].[OrigKeys].[Key] = [TS2].[Key]
FROM [DB].[dbo].[BackupMachine] AS TM2
    INNER JOIN [DB].[dbo].[BackupKeys] TS2 ON [TS2].[Id] = [TM2].[Id]
    INNER JOIN [DB].[dbo].[OrigMachine] TM1 ON [TM1].[Name] = [TM2].[Name]
WHERE [TM1].[Name] = [TM2].[Name];

What am i missing?

Upvotes: 1

Views: 47

Answers (2)

DarkRob
DarkRob

Reputation: 3833

Try this:

UPDATE Org  
SET Org.[Key] = TS2.[Key]
FROM [DB].[dbo].[BackupMachine] AS TM2
INNER JOIN [DB].[dbo].[BackupKeys] TS2 ON [TS2].[Id] = [TM2].[Id]
INNER JOIN [DB].[dbo].[OrigMachine] TM1 ON [TM1].[Name] = [TM2].[Name]
INNER JOIN [DB].[dbo].[OrigKeys] AS Org ON Org.ID = TM1.ID;

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

You can try below - you need to specify the alias name in the time of update as you've defined the alias for your table name

UPDATE TM1 
SET TM1.[Key] = TS2.[Key]
FROM [DB].[dbo].[BackupMachine] AS TM2
    INNER JOIN [DB].[dbo].[BackupKeys] TS2 ON TS2.[Id] = TM2.[Id]
    INNER JOIN [DB].[dbo].[OrigMachine] TM1 ON TM1.[Name] = TM2.[Name]

Upvotes: 0

Related Questions