[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • Last Modified:

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.
0
DeathbySQL
Asked:
DeathbySQL
  • 4
  • 4
  • 2
1 Solution
 
lcohanDatabase AnalystCommented:
You would need to do something like:

update ITEM set cost = ITEM.ParentQuantity / PARENT.cost
from PARENT
where PARENT.id = ITEM.ParentItem
0
 
lcohanDatabase AnalystCommented:
sorry...other way around for division:

update ITEM set cost = PARENT.cost / ITEM.ParentQuantity
from PARENT
where PARENT.id = ITEM.ParentItem
0
 
DeathbySQLAuthor Commented:
Ico,

There is no PARENT Table.

all the records are in the ITEM table.

thanks,
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
DeathbySQLAuthor Commented:
I was thinking something like
update item set item.cost = (SELECT Item.cost/Item.ParentQuantity
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.
0
 
Ephraim WangoyaCommented:
try

update table1
set cost  = (select B.Cost / parentQuantity
             from table1 B
             where BARCODE.ID=Table1.ParentItem)
where ID= 75
0
 
Ephraim WangoyaCommented:
sorry

try

update table1
set cost  = (select B.Cost / parentQuantity
             from table1 B
             where B.ID=Table1.ParentItem)
where ID= 75
0
 
lcohanDatabase AnalystCommented:
Simple like below:


update ITEM with (rowlock) set cost = i.cost / ITEM.ParentQuantity
from ITEM i  with (nolock)
where i.id = ITEM.ParentItem
0
 
lcohanDatabase AnalystCommented:
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.
0
 
DeathbySQLAuthor Commented:
@ 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
0
 
DeathbySQLAuthor Commented:
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<>''
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now