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)
 Update Certain Fields from one table to another

Author  Topic 

rwaldron
Posting Yak Master

131 Posts

Posted - 2006-09-28 : 07:04:27
Hi all,
I have 2 tables , tbl_A & tbl_B
tbl_A Contains 3 columns order_no,company,rep
tbl_B Conatins 4 columns order_no,company,rep, comments.

order_no is unique

tbl_A is dropped and recreated every minutes from a select into statement so that I Have a "Live" view of our system.

I want columns order_no,company,rep in tbl_A to
update Columns order_no,company,rep in tbl_B

what is the best query for this please..
I have tried the following Query

update tbl_B set tbl_B.rep = tbl_A.rep
from tbl_A
WHERE tbl_A.order_no =tbl_B.order_no

this works ok for orders that exist in both tables but this is not
going to work if a new order is added to tbl_A

I think I need to declare a variable?
Can someone please help

Thx,
Ray..

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-28 : 07:13:26
If new row is added to Tbl_A, then how can you update those contents in Tbl_B without first inserting new row...may be this is what you want:

update B
set B.rep = A.rep
B.company = A.company
from Tbl_B B Right Join Tbl_A A
on B.order_no = A.order_no


Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

rwaldron
Posting Yak Master

131 Posts

Posted - 2006-09-28 : 08:02:03
Hiya,
Yes I understand what you are saying..
I added another record to tbl_A Manually
tbl_A has 409 records while tbl_B has 408

I ran the code you suggested

update B
set B.rep = A.rep
from tbl_B B Right join tbl_A A
ON B.order_no = A.order_no

Result is 408 row(s) effected.
tbl_B hasn't picked up the additional Order ?
At one point order_no will only exist in tbl_A

Any other help please..
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-28 : 08:09:25
quote:
Originally posted by rwaldron

Hiya,
Yes I understand what you are saying..
I added another record to tbl_A Manually
tbl_A has 409 records while tbl_B has 408

I ran the code you suggested

update B
set B.rep = A.rep
from tbl_B B Right join tbl_A A
ON B.order_no = A.order_no

Result is 408 row(s) effected.
tbl_B hasn't picked up the additional Order ?
At one point order_no will only exist in tbl_A

Any other help please..




That's what I suspected...because you are adding new row to Tbl_A, you can't have that row in Tbl_B by update statement...you will have to run insert statement to add new added records like this:

Insert into Tbl_B(Order_No, Company, Rep)
Select Order_No, Company, Rep
from Tbl_A A Left Join Tbl_B B
on A.order_no = B.order_no
Where B.Order_No Is Null


Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

rwaldron
Posting Yak Master

131 Posts

Posted - 2006-09-28 : 12:23:31
Hiya,
Thx for your help.
Your first query helps me update any changes to existing records.
Your 2nd query helps me update any new records added.

So I assume that it is ok to combine the 2 so that I cover everything.

Below works.....Do you think its ok to go with this ?

update B
set B.rep = A.rep
from tbl_B B Right Join tbl_A A
ON B.order_no = A.order_no

insert into tbl_B(order_no,rep)
select A.order_no,A.rep
from tbl_A A left Join tbl_B B
ON A.order_no = B.order_no
Where B.order_no Is Null

Go to Top of Page
   

- Advertisement -