February 9, 2009 at 4:11 pm
I want to replace some of the rows in one table with data from another table.
For discussion sake, Table A has 5 columns. Item,Qty, Date, ID, and Type. Table B has the same columns. The are related by the ID column. There are 0 to many rows in B for each row in A.
If A.Type = 'T' AND A.ID exists in B.id AND A.qty = SUM(b.qty), Replace the single row in Table A with all the rows in Table B where A.ID = B.ID
This does not have to be a true replace, I can copy all the data from A that does not exist in B to a new table and also copy the records from B that are related to A to the new table. I was hoping for a more elegant solution than that.
Any suggestions would be appreciated. Thanks.
February 9, 2009 at 10:33 pm
Do you mean that you want the same number of rows in A as in B for each ID?
An outer join will help you figure out which rows are not in A that are in B. We like to see you try a few SQL statements and we can help rather than us writing it for you.
February 10, 2009 at 8:20 am
I found a solution that will work. I couldn't get my mind around replacing a single row in table A with multipe rows from table B. But after thinking about the second option, selecting to a new table, I came up with the code below. Thought I would post it in case someone else has this challenge in the future.
/* Insert the MTData and TOAI data into the result set */
INSERT INTO #ReportSet (
item
, description
, lottracked
, lot
, whse
, cost
, qty
, agedate
, type
)
SELECT
MTData.item
, MTData.description
, MTData.LotTracked
, MTData.Lot
, MTData.whse
, CASE
WHEN TOIASum.mt_trans_num IS NOT NULL THEN TOIA.cost
ELSE MTData.cost
END AS cost
, CASE
WHEN TOIASum.mt_trans_num IS NOT NULL THEN TOIA.qty
ELSE MTData.AgeQty
END AS AgeQty
, CASE
WHEN TOIASum.mt_trans_num IS NOT NULL THEN TOIA.AgeDate
ELSE MTData.trans_date
END AS AgeDate
, CASE
WHEN TOIASum.mt_trans_num IS NOT NULL THEN TOIA.Inv_Type
ELSE MTData.trans_type
END AS Type
FROM @MTData MTData
/* Join in transfer order item age data */
LEFT OUTER JOIN TOIA ON TOIA.item = MTData.item
AND TOIA.ToWhse = MTData.whse
AND TOIA.mt_trans_num = MTData.trans_num
/* Join in a summary qty of the transer order item age receipts.
If receipt qty <> transfer order qty, use the transfer order age and qty */
LEFT OUTER JOIN (
SELECT
mt_trans_num
, SUM(qty) AS TotQty
FROM TOIA
WHERE type = 'R'
GROUP BY mt_trans_num
) TOIASum ON TOIASum.mt_trans_num = MTData.trans_num AND TOIASum.TotQty = MTData.AgeQty
ORDER BY whse, item, agedate
February 10, 2009 at 10:43 am
Do you want to replace 1 record in Table A with Multiple records from Table B? or want to do group by operations on Table B rows which has mapping in Table A and then replace only that row in Table A??
Thanks
Vijaya Kadiyala
February 10, 2009 at 10:51 am
I would like to replace the record in table A with the contents of multiple records from Table B. So Table A might have 5 records. One of the 5 in A is associated to 2 records in Table B. When the replace is finished, there would be 6 (5-1+2) records in Table A and Table B is unchanged.
I was thinking that I would need to do an insert of the 2 records then the removal of the 1 record. But this strategy seemed sensitive especially when you add the fact that I need to do qty validation first. The 2 records in B need to sum to the single record in A. If they do not, then don't do anything.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply