copy a column from one table to another

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

copy a column from one table to another

Author

Topic

newbiePHP
Starting Member

10 Posts

Posted - 2008-10-09 : 06:13:12

Hi guys,

Table A has a column called 'status'. I want table B to have this column to.

So i created a column in Table B called 'status' and am running a query to update the column and just wanted to make sure that it was right b4 making the change.


UPDATE    B
SET      Status =(SELECT Status
                  FROM     A   )

Will that work?

thanks for help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-09 : 06:15:21

UPDATE t1
SET t1.Status =t2.Status
FROM B as t1 inner join A as t2
on t1.unique_col=t2.unique_col

Madhivanan

Failing to plan is Planning to fail

newbiePHP
Starting Member

10 Posts

Posted - 2008-10-09 : 06:20:49

Hi Madhivanan,

Thanks for the quick reply.

Just trying to figure out your Query.

The first 3 lines I get but why do you use

on t1.unique_col=t2.unique_col

what does 'unique_col' do?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-09 : 06:23:33

quote:
Originally posted by newbiePHP

Hi Madhivanan,

Thanks for the quick reply.

Just trying to figure out your Query.

The first 3 lines I get but why do you use

on t1.unique_col=t2.unique_col

what does 'unique_col' do?

unique_col is actually the column names which are unique in both the table A and B
You need to use them in place of unique_col

Madhivanan

Failing to plan is Planning to fail

newbiePHP
Starting Member

10 Posts

Posted - 2008-10-09 : 06:27:48

ok.....getting a little confused.

So I have 2 tables called A and B. Both tables have a column called 'Status'

So your query will be:

UPDATE t1
SET t1.Status =t2.Status
FROM B as t1 inner join A as t2
on t1.Status=t2.Status

Is that right ???

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-10-09 : 06:36:44

conceptually you need to understand how the 2 tables are related to one another. how do you know which status should be set for each row in your first table?

maybe some sample data from your tables would help us illustrate the point to you

Em

newbiePHP
Starting Member

10 Posts

Posted - 2008-10-09 : 06:46:59

Hi elancaster,

Basically that database is the back end of a web page and all the data comes from there.

Recently I have noticed i need another status column in Table B to be able to do more queries.

So table A looks like:

OrderID CreateData SubmitterID Status
601 01/02/03 john AP
602 02/02/03 mike AC
603 03/02/03 jane AP

Table B looks like

OrderID OrderItemID
601 542
601 543
602 544
603 545

So I have now added in an extra column in B called status and need to update that column with the correct status as seen in A.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-10-09 : 06:49:22

so you need to join it on OrderID, so that the right status's are assigned to the right orders. make sense?

Em

newbiePHP
Starting Member

10 Posts

Posted - 2008-10-09 : 06:53:25

ah right so the query is:

UPDATE t1
SET t1.Status =t2.Status
FROM B as t1 inner join A as t2
on t1.OrderID=t2.OrderID

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-09 : 06:58:31

quote:
Originally posted by newbiePHP

ah right so the query is:

UPDATE t1
SET t1.Status =t2.Status
FROM B as t1 inner join A as t2
on t1.OrderID=t2.OrderID

Yes it is

Madhivanan

Failing to plan is Planning to fail

newbiePHP
Starting Member

10 Posts

Posted - 2008-10-09 : 06:59:48

Thanks a lot guys, appricate all the help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources