Is This Complex Update Possible?
-
I am trying to take the result of a caculation of two fields in one table and place it into a field in another table? Is this possible? I have two tables StoreItems and TicketItems. I need to get a unit cost from my StoreItems table by dividing the cost for an entire case (CaseCost) by the case count (CaseCount) and then placing that result into the Cost field of an item in the TicketItems table with a corresponding PLU (like an SKU). PLU and StoreID are the two fields in the tables that link them together. In a nutshell I need this: StoreItems.Cost = TicketItems.CaseCost / TicketItems.CaseCount Keep in mind that I need to run this on every record in the two tables so just passing in a StoreID and PLU is not enough. This has to be done across the board--that is, every TicketItems record needs to have its Cost column set when this has finished running. Thank you. -Matt ------------------------------------------ The 3 great virtues of a programmer: Laziness, Impatience, and Hubris. --Larry Wall
-
I am trying to take the result of a caculation of two fields in one table and place it into a field in another table? Is this possible? I have two tables StoreItems and TicketItems. I need to get a unit cost from my StoreItems table by dividing the cost for an entire case (CaseCost) by the case count (CaseCount) and then placing that result into the Cost field of an item in the TicketItems table with a corresponding PLU (like an SKU). PLU and StoreID are the two fields in the tables that link them together. In a nutshell I need this: StoreItems.Cost = TicketItems.CaseCost / TicketItems.CaseCount Keep in mind that I need to run this on every record in the two tables so just passing in a StoreID and PLU is not enough. This has to be done across the board--that is, every TicketItems record needs to have its Cost column set when this has finished running. Thank you. -Matt ------------------------------------------ The 3 great virtues of a programmer: Laziness, Impatience, and Hubris. --Larry Wall
Off the top of my head the following should do what you want. [EDIT] Oops. I made a mistake the Cost field you are updating is in the StoreItems table, the original query was updating the TicketItems table :-O [/EDIT]
UPDATE StoreItems
SET Cost = TicketItems.CaseCost / TicketItems.CaseCount
FROM TicketItems, StoreItems
WHERE TicketItems.PLU = StoreItems.PLUYou can get more information in your SQL Server books online, look up the index for
UPDATE, UPDATE (described)Does this help?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell The Second EuroCPian Event will be in Brussels on the 4th of September Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
-
Off the top of my head the following should do what you want. [EDIT] Oops. I made a mistake the Cost field you are updating is in the StoreItems table, the original query was updating the TicketItems table :-O [/EDIT]
UPDATE StoreItems
SET Cost = TicketItems.CaseCost / TicketItems.CaseCount
FROM TicketItems, StoreItems
WHERE TicketItems.PLU = StoreItems.PLUYou can get more information in your SQL Server books online, look up the index for
UPDATE, UPDATE (described)Does this help?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell The Second EuroCPian Event will be in Brussels on the 4th of September Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
Actually, no you were right the first time. The cost field is in the TicketItems table not the StoreItems table. So what did that query look like? -Matt ------------------------------------------ The 3 great virtues of a programmer: Laziness, Impatience, and Hubris. --Larry Wall
-
Actually, no you were right the first time. The cost field is in the TicketItems table not the StoreItems table. So what did that query look like? -Matt ------------------------------------------ The 3 great virtues of a programmer: Laziness, Impatience, and Hubris. --Larry Wall
Almost the same actually:
UPDATE TicketItems
SET Cost = TicketItems.CaseCost / TicketItems.CaseCount
FROM TicketItems, StoreItems
WHERE TicketItems.PLU = StoreItems.PLUI'm now a bit confused, which table do the CaseCost and CaseCount items reside? If it is StoreItems then the query will look like this instead:
UPDATE TicketItems
SET Cost = StoreItems.CaseCost / StoreItems.CaseCount
FROM TicketItems, StoreItems
WHERE TicketItems.PLU = StoreItems.PLUThe second query assumes: TicketItems.PLU TicketItems.Cost StoreItems.PLU StoreItems.CastCost StoreItems.CaseCount PLU is the common field that joins the two tables. This update will update every row in the TicketItems table. Does this help? Or have I confused you in return?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell The Second EuroCPian Event will be in Brussels on the 4th of September Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
-
Almost the same actually:
UPDATE TicketItems
SET Cost = TicketItems.CaseCost / TicketItems.CaseCount
FROM TicketItems, StoreItems
WHERE TicketItems.PLU = StoreItems.PLUI'm now a bit confused, which table do the CaseCost and CaseCount items reside? If it is StoreItems then the query will look like this instead:
UPDATE TicketItems
SET Cost = StoreItems.CaseCost / StoreItems.CaseCount
FROM TicketItems, StoreItems
WHERE TicketItems.PLU = StoreItems.PLUThe second query assumes: TicketItems.PLU TicketItems.Cost StoreItems.PLU StoreItems.CastCost StoreItems.CaseCount PLU is the common field that joins the two tables. This update will update every row in the TicketItems table. Does this help? Or have I confused you in return?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell The Second EuroCPian Event will be in Brussels on the 4th of September Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
I think the second one is right. Let me try it out. Thanks a lot for your help. -Matt ------------------------------------------ The 3 great virtues of a programmer: Laziness, Impatience, and Hubris. --Larry Wall
-
Almost the same actually:
UPDATE TicketItems
SET Cost = TicketItems.CaseCost / TicketItems.CaseCount
FROM TicketItems, StoreItems
WHERE TicketItems.PLU = StoreItems.PLUI'm now a bit confused, which table do the CaseCost and CaseCount items reside? If it is StoreItems then the query will look like this instead:
UPDATE TicketItems
SET Cost = StoreItems.CaseCost / StoreItems.CaseCount
FROM TicketItems, StoreItems
WHERE TicketItems.PLU = StoreItems.PLUThe second query assumes: TicketItems.PLU TicketItems.Cost StoreItems.PLU StoreItems.CastCost StoreItems.CaseCount PLU is the common field that joins the two tables. This update will update every row in the TicketItems table. Does this help? Or have I confused you in return?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell The Second EuroCPian Event will be in Brussels on the 4th of September Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
It worked and I understand it. You're a life saver!! Thank you!! -Matt ------------------------------------------ The 3 great virtues of a programmer: Laziness, Impatience, and Hubris. --Larry Wall
-
It worked and I understand it. You're a life saver!! Thank you!! -Matt ------------------------------------------ The 3 great virtues of a programmer: Laziness, Impatience, and Hubris. --Larry Wall
perlmunger wrote: It worked and I understand it. Excellent. :-D
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell The Second EuroCPian Event will be in Brussels on the 4th of September Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
-
I am trying to take the result of a caculation of two fields in one table and place it into a field in another table? Is this possible? I have two tables StoreItems and TicketItems. I need to get a unit cost from my StoreItems table by dividing the cost for an entire case (CaseCost) by the case count (CaseCount) and then placing that result into the Cost field of an item in the TicketItems table with a corresponding PLU (like an SKU). PLU and StoreID are the two fields in the tables that link them together. In a nutshell I need this: StoreItems.Cost = TicketItems.CaseCost / TicketItems.CaseCount Keep in mind that I need to run this on every record in the two tables so just passing in a StoreID and PLU is not enough. This has to be done across the board--that is, every TicketItems record needs to have its Cost column set when this has finished running. Thank you. -Matt ------------------------------------------ The 3 great virtues of a programmer: Laziness, Impatience, and Hubris. --Larry Wall
-
Yes . you can update from one or more table ., Try this sql. may be want to change some col names. I think you get the idea.. ;) UPDATE StoreItems SET Cost = T.CaseCost / T.CaseCount FROM StoreItems S INNER JOIN TicketItems T ON S.PLU = T.PLU HTH D!shan