September 4, 2018 at 10:29 am
I have a table with a clolumn name 'CompanyNid' which I am not able to update from the staging table. Below is a query I am using
update [PMG].[ContactData]
set CompanyNids = s.CompanyNids
from [PMG].[ContactData] c
inner join [Staging].[StgInitialCompanydata] s on c.CompanyNids = s.CompanyNids
I am not sure what I am missing.
September 4, 2018 at 10:38 am
That query doesn't make sense. You're doing an inner join based on "CompanyNids", so the values have to be equal for matching rows to be selected. But then you're setting the value of one table's CompanyNids column: the CompanyNids column must already be the same value or they wouldn't have joined to each other in the first place, so the update is unnecessary.
Also, very important, UPDATE the alias only when updating tables in a join:
update C
set CompanyNids = s.CompanyNids
from [PMG].[ContactData] c
inner join [Staging].[StgInitialCompanydata] s on c.CompanyNids = s.CompanyNids
UPDATEing the original table name could lead to invalid values being UPDATEd, which is likely what you're seeing as the "problem".
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 4, 2018 at 10:39 am
NewBornDBA2017 - Tuesday, September 4, 2018 10:29 AMI have a table with a clolumn name 'CompanyNid' which I am not able to update from the staging table. Below is a query I am using
update [PMG].[ContactData]
set CompanyNids = s.CompanyNids
from [PMG].[ContactData] c
inner join [Staging].[StgInitialCompanydata] s on c.CompanyNids = s.CompanyNids
I am not sure what I am missing.
You are joining on the same column you are updating. So the only updates which will happen are those where there is already a match between the two tables.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 4, 2018 at 10:57 am
of course. Silly me. Its all good.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply