Update column based on multiple table inner join

Question

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?

Please add some sample data to your question which explains what you are trying to update. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 23, 2019 at 5:57

DarkRob · Accepted Answer · 2019-07-23 06:44:33Z

1

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;

edited Jul 23, 2019 at 6:44

answered Jul 23, 2019 at 6:05

DarkRob

3,8231 gold badge12 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jimson James Over a year ago

There is no TM2.[Key], meant to say TS2.key?

DarkRob Over a year ago

so you are saying there is no key exists in your backupkeys table?

Jimson James Over a year ago

Updated the question with sample data. btw, there is no relationship b/w the ID's of original and backup tables. i.e, origmachine.id != bakmachine.id.

DarkRob Over a year ago

we are not mapping anywhere ID's of original and backup tables. i.e, origmachine.id != bakmachine.id. We are mapping them on the basis of name's of original and backup tables. i.e, origmachine.name = bakmachine.name. I think atleast that relation is valid.

Fahmi · Accepted Answer · 2019-07-23 06:15:15Z

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]

Collectives™ on Stack Overflow

Update column based on multiple table inner join

2 Answers 2

4 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Related