CODEWITHSUNDEEP
sql-serverjoinsql-updatesubquery
Shawn Gregory
Shawn Gregory

Reputation: 1

I receive multiple errors in SQL Server when trying to update a column in one table with a column for another

I have two tables dim_distributor and distributor_variant.

There is a distributorvariantname column in distributor_variant, and a distributorname column in dim_distributor that should contain identical values between the two tables.

Both tables have a distributorid column that should contain identical values between the two tables.

There are some distributorid fields that are set to 0 in the distributor_variant table, and I want to update them to the same distributorid values in the dim_distributor table.

There is also a distributorvariantid column in distributor_variant, and I do not want to update the one that is set to 0.

Below are the two queries I wrote to try to accomplish this.

The below query gives me a

The multi-part identifier "dv.DistributorID" could not be bound.

I checked spelling, what am I missing here? 

update distributor_variant
set dv.distributorID = dd.distributorID
from distributor_variant dv
join dim_distributor dd on dd.distributorname = dv.distributorvariantname
where dv.distributorID =0 and dv.distributorVariantID !=0

The below query gives me

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I tried in before the parentheses and received an incorrect syntax before the keyword 'in' error. 

update distributor_variant 
set distributorID = (select dd.distributorID 
from dim_distributor dd, distributor_variant dv 
where dd.DistributorName = dv.distributorVariantName
and dv.distributorID =0 and dv.distributorVariantID !=0)

What am I doing wrong?

Any help would be much appreciated.

Upvotes: 0

Views: 33

Answers (1)

Xedni
Xedni

Reputation: 4695

Updating with a join requires some weird syntax. You have to say update <alias> (no actual table name) before you've actually stated what that alias is actually tied to. Try this instead:

update dv
 set dv.distributorID = dd.distributorID 
 from distributor_variant dv 
 join dim_distributor dd 
 on dd.distributorname = dv.distributorvariantname 
 where dv.distributorID =0 and dv.distributorVariantID !=0

Upvotes: 2

Related Questions