DeathbySQL
asked on
update with a select
Hello all,
I need to update the cost of the child item (ID 75) with the cost from the parrent Item divided by the parent quantity. in this case it would be 24/6 . this is all in the same table (item).
select ID, ParentItem, ParentQuantity, cost from ITEM
results:
ID ParentItem ParentQuantity cost
75, 357, 6, 2
357, 0, 0, 24
so after the querry the child item would cost $4
good luck and thanks in advance.
I need to update the cost of the child item (ID 75) with the cost from the parrent Item divided by the parent quantity. in this case it would be 24/6 . this is all in the same table (item).
select ID, ParentItem, ParentQuantity, cost from ITEM
results:
ID ParentItem ParentQuantity cost
75, 357, 6, 2
357, 0, 0, 24
so after the querry the child item would cost $4
good luck and thanks in advance.
sorry...other way around for division:
update ITEM set cost = PARENT.cost / ITEM.ParentQuantity
from PARENT
where PARENT.id = ITEM.ParentItem
update ITEM set cost = PARENT.cost / ITEM.ParentQuantity
from PARENT
where PARENT.id = ITEM.ParentItem
ASKER
Ico,
There is no PARENT Table.
all the records are in the ITEM table.
thanks,
There is no PARENT Table.
all the records are in the ITEM table.
thanks,
ASKER
I was thinking something like
update item set item.cost = (SELECT Item.cost/Item.ParentQuant
FROM Item
WHERE Item.id = Item.Parentitem)
but I want item.id = item.parentitem to search all the records for a match..there will only be one that matchs.
update item set item.cost = (SELECT Item.cost/Item.ParentQuant
FROM Item
WHERE Item.id = Item.Parentitem)
but I want item.id = item.parentitem to search all the records for a match..there will only be one that matchs.
try
update table1
set cost = (select B.Cost / parentQuantity
from table1 B
where BARCODE.ID=Table1.ParentIt
where ID= 75
update table1
set cost = (select B.Cost / parentQuantity
from table1 B
where BARCODE.ID=Table1.ParentIt
where ID= 75
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Simple like below:
update ITEM with (rowlock) set cost = i.cost / ITEM.ParentQuantity
from ITEM i with (nolock)
where i.id = ITEM.ParentItem
update ITEM with (rowlock) set cost = i.cost / ITEM.ParentQuantity
from ITEM i with (nolock)
where i.id = ITEM.ParentItem
Oh - not sure how big that table is and if there are any triggers on UPDATE so either way I suggest you batch your update.
ASKER
@ Icohan
its not a very large table and there is no trigers on update, not sure about batching.
error recieved on you last attempt
The multi-part identifier "ITEM.ParentItem" could not be bound.
thanks
its not a very large table and there is no trigers on update, not sure about batching.
error recieved on you last attempt
The multi-part identifier "ITEM.ParentItem" could not be bound.
thanks
ASKER
Little Mod needed but got me close enough..awesome
update ITEM
set cost = (select B.Cost / Item.parentQuantity
from ITEM B
where B.ID=ITEM.ParentItem)
where Item.ParentItem<>''
update ITEM
set cost = (select B.Cost / Item.parentQuantity
from ITEM B
where B.ID=ITEM.ParentItem)
where Item.ParentItem<>''
update ITEM set cost = ITEM.ParentQuantity / PARENT.cost
from PARENT
where PARENT.id = ITEM.ParentItem