using Right join after an Update

Question

Post reply

using Right join after an Update

Red27

SSC Veteran

Points: 234
More actions
October 16, 2017 at 9:47 am

#324089

Hi guys,
Can you help with the code below.
Update [Branch]
right join [Addresses] on [Branch].reference= [Addresses].ID Set [Branch] Addresses 1 =Addresses.Addresses1 [Branch] Addresses 2 = Addresses.Addresses2 [Branch] Addresses 3 =Addresses.Addresses3 [Branch] Addresses 4 =Addresses.Addresses4 [Branch] PostCode =Addresses.postcode [Branch] Fax =Addresses.FaxAddresses [Branch] Number = number
the error message is the multi part identifier could not be bound

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Please check the syntax of the UPDATE statement. The JOINs go at the end, after the assignments, and you need a FROM.

John

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

adan950 - Monday, October 16, 2017 9:47 AM
Hi guys,
Can you help with the code below.
Update [Branch]
right join [Addresses] on [Branch].reference= [Addresses].ID Set [Branch] Addresses 1 =Addresses.Addresses1 [Branch] Addresses 2 = Addresses.Addresses2 [Branch] Addresses 3 =Addresses.Addresses3 [Branch] Addresses 4 =Addresses.Addresses4 [Branch] PostCode =Addresses.postcode [Branch] Fax =Addresses.FaxAddresses [Branch] Number = number
the error message is the multi part identifier could not be bound

Something like this (not tested):
UPDATE [br] SET [Addresses 1] = [a].[Addresses1] [Addresses 2] = [a].[Addresses2] [Addresses 3] = [a].[Addresses3] [Addresses 4] = [a].[Addresses4] [PostCode] = [a].[postcode] [Fax] = [a].[FaxAddresses] [Number] = [a].[number] -- guessing at the alias needed on this line FROM [Branch] AS [br] RIGHT OUTER JOIN [Addresses] AS [a] ON [br].[reference] = [a].[ID];

Red27 SSC Veteran Points: 234 More actions · Answer 3

Red27

SSC Veteran

Points: 234

October 16, 2017 at 10:16 am

#1963876

thanks guys. it works.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Is there any reason why you're using a RIGHT OUTER JOIN? Updating the outer table of that join means you're going to be attempting to update rows that don't exist. Either you're going to get an error, or those rows are just going to be ignored. You should verify that an inner join gives the same results, and use that instead - it will make it much easier for anyone else (or your future else) who tries to understand the query!

John