Solved

Update a table from another table

Posted on 2009-07-06
14
175 Views
Last Modified: 2012-05-07
Hi,

I have 2 tables

TableOne  :

1. FromWarehouse
2. ToWarehouse
3. Qty


TableTwo :

1. Warehouse
2. QtyIn
3. QtyOut

Sample:

TableOne:

FromWarehouse    ToWarehouse   Qty
A                             B                       10

TableTwo:

Warehouse    QtyIn     QtyOut
A                     0             10
B                     10           0

How to update TableTwo using TableOne?

Thank you.



0
Comment
Question by:emi_sastra
  • 5
  • 4
  • 3
  • +2
14 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
Update t2
SET QtyOut = QtyOut + qty
FROM table2 t2 INNER JOIN Table1 t1 on t1.FromWarehouse     = t2.wareHouse

Update t2
SET QtyOut = QtyOut + qty
FROM table2 t2 INNER JOIN Table1 t1 on t1.ToWarehouse   = t2.wareHouse
0
 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
UPDATE TableTwo
SET  OtyOut = Qty
FRom   TableTwo t INNER JOIN TableOne g
              ON t.Warehouse = g.FromWareHouse
UPDATE TableTwo
SET  OtyIn = Qty
FRom   TableTwo t INNER JOIN TableOne g
              ON t.Warehouse = g.ToWareHouse
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
Hope this helps.
Kindly specify the columns and details so that exact query can be given
udpate table2

set QtyIn = t1.

from table1 t1, table2 t2

where t2.Warehouse = t1.FromWarehouse

Open in new window

0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
Hi All,

I am so sorry, I should mentioned Insert, not Update, since the rows of data could change.
Usually I delete the transaction first then insert it.

Please see my revised table.

TableOne  :

1. TrsId
2. FromWarehouse
3. ToWarehouse
4. ProductCode
5. Qty


TableTwo :

1. TrsId
2. Warehouse
3. ProductCode
4. QtyIn
5. QtyOut

Sample:

TableOne:

TrsId  FromWarehouse    ToWarehouse   ProductCode   Qty
001    A                             B                      CocaCola         10

TableTwo:

TrsId  Warehouse    ProductCode  QtyIn     QtyOut
001    A                     CocaCola       0             10
001    B                     CocaCola       10           0

Thank you.

0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
<< How to update TableTwo using TableOne? >>
which column you want to update and with what value?
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
Hi Sharath_123,

Please see my last post Id 24786018

Thank you.

0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
We both were posting at the same time, hence i missed your last post. so do you want to insert data into second table based on the data in the first table?
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 17

Assisted Solution

by:pssandhu
pssandhu earned 200 total points
Comment Utility
Something like this:
INSERT INTO TableTwo (TrsID, Warehouse, ProductCode, QtyOut)
Select TrsID, FromWarehouse, ProductCode, Qty)
INSERT INTO TableTwo (TrsID, Warehouse, ProductCode, QtyIn)
Select TrsID, ToWarehouse, ProductCode, Qty)
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
Hi Sharath_123,

Yes.
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
Hi pssandhu,


It needs two query to solve this. Correction, you miss the "From TableOne" clause.
Is there any other way to do it?

Thank you.
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
try this
insert into TableTwo

select TrsId,FromWarehouse as WareHouse,ProductCode,0 as QtyIn,Qty as QtyOut from TableOne

union all

select TrsId,FromWarehouse as WareHouse,ProductCode,Qty as QtyIn,0 as QtyOut from TableOne

Open in new window

0
 
LVL 40

Accepted Solution

by:
Sharath earned 300 total points
Comment Utility
typo in previous post
insert into TableTwo

select TrsId,FromWarehouse as WareHouse,ProductCode,0 as QtyIn,Qty as QtyOut from TableOne

union all

select TrsId,ToWarehouse as WareHouse,ProductCode,Qty as QtyIn,0 as QtyOut from TableOne

Open in new window

0
 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
Yes you can use the union clause to achive that as shown by sharath_123 above.
P.
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
Hi pssandhu,

Yes, I prefer Sharath_123 method.

Thank you very much for your help.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now