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.
Author |
Topic |
rwaldron
Posting Yak Master
131 Posts |
Posted - 2006-09-28 : 07:04:27
|
Hi all,I have 2 tables , tbl_A & tbl_Btbl_A Contains 3 columns order_no,company,reptbl_B Conatins 4 columns order_no,company,rep, comments.order_no is uniquetbl_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 toupdate Columns order_no,company,rep in tbl_Bwhat is the best query for this please..I have tried the following Queryupdate tbl_B set tbl_B.rep = tbl_A.rep from tbl_A WHERE tbl_A.order_no =tbl_B.order_nothis works ok for orders that exist in both tables but this is notgoing to work if a new order is added to tbl_AI 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 Bset B.rep = A.rep B.company = A.companyfrom Tbl_B B Right Join Tbl_A Aon B.order_no = A.order_no Harsh AthalyeIndia."Nothing is Impossible" |
|
|
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 Manuallytbl_A has 409 records while tbl_B has 408I ran the code you suggested update Bset B.rep = A.repfrom tbl_B B Right join tbl_A AON B.order_no = A.order_noResult is 408 row(s) effected.tbl_B hasn't picked up the additional Order ?At one point order_no will only exist in tbl_AAny other help please.. |
|
|
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 Manuallytbl_A has 409 records while tbl_B has 408I ran the code you suggested update Bset B.rep = A.repfrom tbl_B B Right join tbl_A AON B.order_no = A.order_noResult is 408 row(s) effected.tbl_B hasn't picked up the additional Order ?At one point order_no will only exist in tbl_AAny 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, Repfrom Tbl_A A Left Join Tbl_B Bon A.order_no = B.order_noWhere B.Order_No Is Null Harsh AthalyeIndia."Nothing is Impossible" |
|
|
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.repfrom tbl_B B Right Join tbl_A AON B.order_no = A.order_noinsert into tbl_B(order_no,rep) select A.order_no,A.repfrom tbl_A A left Join tbl_B BON A.order_no = B.order_noWhere B.order_no Is Null |
|
|
|
|
|
|
|