Help updating a colum with values from another table. 1

[Rene1024]

Hello everyone,

This one is been driving me crazy, my statement looks like this:

I have tables a and b

update dbo.a
set column = b.column
from dbo.a inner join dbo.b
on a.id = b.id

When I run this statement my column gets updated with the text "b.colunm" instead of the value from table b

What am I doing wrong?

Rgds.

Rene.

 

[amrita418]

What is your column name?

Use something like
update a
set a.yourcolumnA = b.yourcolumnB
from yourtableA a inner join yourtableb b
on a.id = b.id

Regards,
AA

 

[SQLBill]

Change the SET to this:

SET column = (SELECT b.column FROM dbo.b)
WHERE a.id = b.id


I believe that should do it.

-SQLBill

Posting advice: FAQ481-4875

 

[jbenson001]

The Code looks like it should work...
SQLBill.. why do another select?

Is the code wrong somehow?

 

[Catadmin]

I'm not SQLBill, but here goes.

The SET keyword takes what is after the = sign literally unless you tell it to go choose the specific value you're looking for. That's why you need to do a subquery Select. Even when you're just setting a variable outside of the Update statement, you need to use either a literal or a Select statement to choose the value.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[jbenson001]

Thanks Catadmin.. that makes sense now, I never have done a set in that way. This is good to know... Thanks guys..

 

[Rene1024]

Thanks for all your replies, it's still not working. Here's my statement:

update j
set j.county = (select county from dbo.counties)
from dbo.june j inner join dbo.counties c
on j.localitycode = c.localitycode

I get the following error:

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

 

[Catadmin]

You're getting the error because the phrase "Select county from dbo.countries" is returning a plethera of values.

Try this:

update j
set j.county = (select county from dbo.counties c
               where j.localitycode = c.localitycode)

Or you can redo the above code to do the join inside the subquery.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[jbenson001]

I belive you need it to be this:

update j
set j.county = (select county 
                from dbo.june j inner join dbo.counties c
                on j.localitycode = c.localitycode)

 

[amrita418]

I still believe a straight update should do:

create table #june (localitycode char(2), county varchar(20))
create table #counties (localitycode char(2), county varchar(20))

insert into #june values ('LA', 'Los Angeles')
insert into #june values ('SD', NULL)
insert into #june values ('PD', NULL)

insert into #counties values ('SD', 'San Diego')
insert into #counties values ('PD', 'Pittsburg')

update 	j
set 	j.county = c.county
from 	#june j, #counties c
where 	j.localitycode = c.localitycode

select * from #june

maybe you can add a clause to update only rows where county values currently do not match.

isnull(j.county, 'X') != c.county

Regards,
AA

 

[Rene1024]

Thanks to all.

I used the code that catadmin suggested and it worked like a charm.

Thanks again.

Rgds.

Rene