April 17, 2013 at 8:58 am
I have two tables, one with (potentially) multiple records of an item with different quantities, and the other table will have only one record for each item and will aggregate the sum total of the quantities - i.e.:
Table 1
ItemQtyMemo
ABC24pending
Table2
ItemQtyMemo
ABC14test
ABC10test
XYZ5test
I need to update the memo in Table1 with the memo from Table2, and would like to find an efficient way to do so. Any advice would be greatly appreciated.
April 17, 2013 at 11:11 am
jerry 5150 (4/17/2013)
I have two tables, one with (potentially) multiple records of an item with different quantities, and the other table will have only one record for each item and will aggregate the sum total of the quantities - i.e.:Table 1
ItemQtyMemo
ABC24pending
Table2
ItemQtyMemo
ABC14test
ABC10test
XYZ5test
I need to update the memo in Table1 with the memo from Table2, and would like to find an efficient way to do so. Any advice would be greatly appreciated.
Hi and welcome to the forums. It is difficult to offer a lot of advice here because we didn't get a lot of details. It is generally a good idea to post ddl, sample data and desired output. You can read about the best practices when posting questions by reading the first link in my signature.
The biggest issue I see here is which row from Table2 do you want to use? In your example the memo values are the same but the reality may be different. Even if they are the same you have to decide which one to use.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 17, 2013 at 11:13 am
Sort of a generic approach. I don't think it compares very favorably to the microsoft specific approach, ie., 'UPDATE FROM'.
Hopefully someone can take my DDL and we can start one of those threads about how we shouldn't use microsoft specific syntax or even berate me for the inefficient subquery! I always enjoy those threads!
edit: LOL supposed to be updating the memo field, my bad!
DROP TABLE #TABLE1
DROP TABLE #TABLE2
CREATE TABLE #TABLE1
(
ITEM VARCHAR(10),
QTY INT,
MEMO VARCHAR(50)
)
CREATE TABLE #TABLE2
(
ITEM VARCHAR(10),
QTY INT,
MEMO VARCHAR(50)
)
INSERT INTO #TABLE1
SELECT 'ABC', 24, 'Pending'
UNION ALL
SELECT 'XYZ',15,'Pending'
INSERT INTO #TABLE2
SELECT 'ABC',14,'test from abc'
UNION ALL
SELECT 'ABC',10,'test from abc 0'
UNION ALL
SELECT 'XYZ',5,'test from xyz'
UNION ALL
SELECT 'XYZ',10,'test from xyz 0'
UPDATE #TABLE1
SET MEMO =
(
SELECT TOP 1 MEMO
FROM #TABLE2
WHERE #TABLE1.ITEM = #TABLE2.ITEM
ORDER BY MEMO -- << -- DETERMINES WHICH MEMO TO UPDATE TO, CHANGE AS APPROPRIATE!
)
SELECT * FROM #TABLE1
April 17, 2013 at 12:26 pm
patrickmcginnis59 10839 (4/17/2013)
Sort of a generic approach. I don't think it compares very favorably to the microsoft specific approach, ie., 'UPDATE FROM'.Hopefully someone can take my DDL and we can start one of those threads about how we shouldn't use microsoft specific syntax or even berate me for the inefficient subquery! I always enjoy those threads!
On the contrary. I applaud you for putting together consumable information. No need to worry about being berated for using MS specific syntax, JC hasn't been around lately. This is a MS specific forum so why not use the tool and all of its abilities?
I tossed this together as another alternative. It may or may not perform any better than the subquery approach.
with MyData as
(
select top 1 t2.MEMO, t1.ITEM
from #TABLE1 t1
join #TABLE2 t2 on t1.ITEM = t2.ITEM
order by t2.MEMO
)
update t1
set memo = d.memo
from #TABLE1 t1
join MyData d on d.ITEM = t1.ITEM
select * from #TABLE1
So now the OP has at least two ways to accomplish the same thing. One is ANSI compliant and would work on pretty much any RDBMS and mine will work ONLY on SS.
--EDIT--
Bitten by the fat finger bug again. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 17, 2013 at 12:53 pm
Sean Lange (4/17/2013)
patrickmcginnis59 10839 (4/17/2013)
Sort of a generic approach. I don't think it compares very favorably to the microsoft specific approach, ie., 'UPDATE FROM'.Hopefully someone can take my DDL and we can start one of those threads about how we shouldn't use microsoft specific syntax or even berate me for the inefficient subquery! I always enjoy those threads!
On the contrary. I applaud you for putting together consumable information. No need to worry about being berated for using MS specific syntax, JC hasn't been around lately. This is a MS specific forum so why not use the tool and all of its abilities?
I tossed this together as another alternative. It may or may not perform any better than the subquery approach.
with MyData as
(
select top 1 t2.MEMO, t1.ITEM
from #TABLE1 t1
join #TABLE2 t2 on t1.ITEM = t2.ITEM
order by t2.MEMO
)
update t1
set memo = d.memo
from #TABLE1 t1
join MyData d on d.ITEM = t1.ITEM
select * from #TABLE1
So now the OP has at least two ways to accomplish the same thing. One is ANSI compliant and would work on pretty much any RDBMS and mine will work ONLY on SS.
--EDIT--
Bitten by the fat finger bug again. 🙂
Oops you're only updating 'ABC' from that one and 'XYZ' remains 'Pending'. I think its because the CTE only really returns 1 row.
April 17, 2013 at 12:58 pm
Thank you all for your suggestions, and I apologize for not reading the instructions on how to post before posting. I think your replies have clarified for me that my data is not setup right, so I will work on resolving this problem by preempting it.
April 17, 2013 at 1:04 pm
patrickmcginnis59 10839 (4/17/2013)
Oops you're only updating 'ABC' from that one and 'XYZ' remains 'Pending'. I think its because the CTE only really returns 1 row.
:blush:
Ahh yes indeed. DOH!!! Easy enough to fix with ROW_NUMBER.
with MyData as
(
select t2.MEMO, t1.ITEM, ROW_NUMBER() over(PARTITION by t1.Item order by t2.MEMO asc) as RowNum
from #TABLE1 t1
join #TABLE2 t2 on t1.ITEM = t2.ITEM
)
update t1
set memo = d.memo
from #TABLE1 t1
join MyData d on d.ITEM = t1.ITEM
where d.RowNum = 1
select * from #TABLE1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 17, 2013 at 1:06 pm
jerry 5150 (4/17/2013)
Thank you all for your suggestions, and I apologize for not reading the instructions on how to post before posting. I think your replies have clarified for me that my data is not setup right, so I will work on resolving this problem by preempting it.
No worries. It is hard to know what to post for these types of things when you haven't posted about it in the past. 😉
For the most part you will find people around here are pretty patient. The people who post on these forums with help do so because they want to, we are all volunteers.
I would agree that your data is not properly normalized in the first place. If you can fix it in the tables, this becomes a non-issue.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply