emi_sastra
asked on
Update a table from another table
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.
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.
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
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
Hope this helps.
Kindly specify the columns and details so that exact query can be given
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
ASKER
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.
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.
<< How to update TableTwo using TableOne? >>
which column you want to update and with what value?
which column you want to update and with what value?
ASKER
Hi Sharath_123,
Please see my last post Id 24786018
Thank you.
Please see my last post Id 24786018
Thank you.
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Sharath_123,
Yes.
Yes.
ASKER
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.
It needs two query to solve this. Correction, you miss the "From TableOne" clause.
Is there any other way to do it?
Thank you.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes you can use the union clause to achive that as shown by sharath_123 above.
P.
P.
ASKER
Hi pssandhu,
Yes, I prefer Sharath_123 method.
Thank you very much for your help.
Yes, I prefer Sharath_123 method.
Thank you very much for your help.
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