We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Update a table from another table

emi_sastra
emi_sastra asked
on
Medium Priority
198 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.



Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
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

Commented:
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
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
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

Author

Commented:
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.

SharathData Engineer
CERTIFIED EXPERT

Commented:
<< How to update TableTwo using TableOne? >>
which column you want to update and with what value?

Author

Commented:
Hi Sharath_123,

Please see my last post Id 24786018

Thank you.

SharathData Engineer
CERTIFIED EXPERT

Commented:
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?
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi Sharath_123,

Yes.

Author

Commented:
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.
SharathData Engineer
CERTIFIED EXPERT

Commented:
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

Data Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
Yes you can use the union clause to achive that as shown by sharath_123 above.
P.

Author

Commented:
Hi pssandhu,

Yes, I prefer Sharath_123 method.

Thank you very much for your help.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.