Insert Data into Column from 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

SQL Server 2000 Forums

Transact-SQL (2000)

Insert Data into Column from another table

Author

Topic

ebferro
Starting Member

4 Posts

Posted - 2005-12-19 : 09:38:09

I'm sure this is easy to do but since I know nothing about t-sql, I thought I'd ask the experts in this forum.
I have a table called production that has a field called part number in it. I want to add the customer name for that part number based on another table called custcrossref that contains the part number field and the customer name.
The production table has a ton of records already in it so I need to do this after the fact.
Thanks for any help that can be offered.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-19 : 09:59:41

Can you post your table structure, sample data and expected result ?

-----------------
[KH]
Learn something new everyday

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-19 : 10:01:29

Select P.PartNumber,C.CustomerName from Production P inner join Customer C on P.partNumber=C.PartNumber

Madhivanan

Failing to plan is Planning to fail

ebferro
Starting Member

4 Posts

Posted - 2005-12-19 : 12:08:29

Khtan and Madhivanan:
The tables look like this
Production - Fields of interest are partnumber and customer Both fields are string fields.
Custcrossref - Fields of interest are partnumber and customer Both fields are string fields.
Right now, the Production table contains data in the part number field only. I need loop through the Production table and look at the part number in every row. When I get the part number from that row in the production table, I need to find the row in the custcrossref table that has partnumber.production equal to partnumber.custcrossref. When I find that row in the custcrossref table, I need to take the customer number that's associated with that row in custcrossref and insert it back into the row that I'm at in the production table. When I'm done, I need the every row in the production table to have the proper customer for the partnumber in that row associated with it. Hope this helps clarify things.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-20 : 00:41:26

You need Joins
Refer the query I posted

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-20 : 00:48:42

ebferro, Madhivanan query should works. If you did not get the result you required, then post some sample data and the expectd result.

-----------------
[KH]
Learn something new everyday

ebferro
Starting Member

4 Posts

Posted - 2006-01-11 : 11:45:29

Gentlemen:
Thanks for the query. It does exactly what I want except for setting the customer.production field in the production table equal to the value in the customer.crossreference field. How do I go about doing that in T-sql?

avishivani
Starting Member

4 Posts

Posted - 2006-01-12 : 05:51:21

This should solve your problem:

update production
set customer=(select customer from custcrossref where production.partnumber=custcrossref.partnumber)

Thanks

ebferro
Starting Member

4 Posts

Posted - 2006-01-12 : 07:32:26

Thanks for the help. If I need to update two fields at the same time, how do I do that? I've tried a second set = statement that applies to the second field but I get a syntax error at the = sign message. I've commented the first field update line and tried the second without any syntax changes and it works just fine so I don't think it's a syntax error. Thanks for the help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-12 : 07:38:24

use these syntax


update t1
    set col1 = t2.cola,
        col2 = t3.colb
from table1 t1 inner join table2 t2
    on t1.pk = t2.pk

-----------------
'KH'
Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-12 : 07:51:03

Better to use alias name in the assigned columns as well

update t1
    set t1.col1 = t2.cola,
        t1.col2 = t2.colb
from table1 t1 inner join table2 t2
    on t1.pk = t2.pk

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources