mapping infromation from a table to another table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

mapping infromation from a table to another table

Author

Topic

goligol
Posting Yak Master

128 Posts

Posted - 2011-10-13 : 12:57:48

Hi, I have a table1 which only has one column, and another table2 which is more complete and has more information.
I am adding a column to table1 (descriptions) and trying to retrieve information form table2 realated to the rows in the table1:

select a.hs_code
,a.descriptions = CASE
WHEN a.hs_code = b.hs_code THEN a.descriptions = b.hs_code_desc
END

from table1 a
, table2 b

any clue?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-13 : 13:07:30

[code]
update a
set a.descriptions = b.hs_code_desc
from table1 a
inner join table2 b
on a.hs_code = b.hs_code
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

goligol
Posting Yak Master

128 Posts

Posted - 2011-10-13 : 13:20:57

it still generate error:

select a.hs_code
,a.descriptions

update a
set a.description = b.hs_code_desc
from table1 a
inner join table2 b
on a.hs_code = b.hs_code

Error:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "a.hs_code" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "a.descriptions" could not be bound.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'descriptions'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-13 : 13:33:15

no need of select just use



update a
set a.description = b.hs_code_desc 
from table1 a
inner join table2 b
on a.hs_code = b.hs_code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

goligol
Posting Yak Master

128 Posts

Posted - 2011-10-13 : 13:55:15

when I use the above commends is still shows the following error:

Invalid column name 'hs_code_desc'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 01:20:23

do you've the column 'hs_code_desc' in table2?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

goligol
Posting Yak Master

128 Posts

Posted - 2011-10-14 : 11:13:06

Yes I have it in table2 but in table1 I have to generate this coulum and make it equal to a'hs_code_desc' in table2 when a.hs_code = b.hs_code.

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 11:15:23

what do you mean by generate? do you mean you need to add it? then use like


alter table table1 add hs_code_desc <data type same as in table2> null

update a
set a.hs_code_desc = b.hs_code_desc 
from table1 a
inner join table2 b
on a.hs_code = b.hs_code

make sure you give data type same as table2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources