[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Update a table from another table

Posted on 2009-07-06
14
Medium Priority
?
185 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +2
14 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24785893
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
ID: 24785896
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
ID: 24785897
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:emi_sastra
ID: 24786018
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 41

Expert Comment

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

Author Comment

by:emi_sastra
ID: 24786066
Hi Sharath_123,

Please see my last post Id 24786018

Thank you.

0
 
LVL 41

Expert Comment

by:Sharath
ID: 24786109
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
 
LVL 17

Assisted Solution

by:pssandhu
pssandhu earned 800 total points
ID: 24786130
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
ID: 24786145
Hi Sharath_123,

Yes.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24786235
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 41

Expert Comment

by:Sharath
ID: 24786250
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 41

Accepted Solution

by:
Sharath earned 1200 total points
ID: 24786271
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
ID: 24786279
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
ID: 24786327
Hi pssandhu,

Yes, I prefer Sharath_123 method.

Thank you very much for your help.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

649 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