January 16, 2014 at 2:29 pm
Hi everyone. Hope you are having a super day.
I have a #table with Customer number, Customer Order Number and the Order class. I want to go through the table to find all of the same Customer Numbers(CustNo) and check the Order class value. They want me to change the value of order class to MTO if even 1 MTO order class exists for that customer number. So in the following example I found that the 3rd Customer Order Number has a class value of MTO, So in this case I would have to go back and update the previous two records Order Class to MTO. Would it be better to do this with a Do While or a cursor?
custNo co_num order_class
1000388 18290 QS
1000388 18292 QS
1000388 18297 MTO
Thanks very much.
Gillian
January 16, 2014 at 2:51 pm
Neither. You should be able to accomplish this with a single UPDATE statement.
You can either use a sub SELECT in the WHERE clause, or a self join to filter the data.
Give it a try, and let me know if you get stuck.
Also, it's considered best practice on this forum to post DDL with sample data. This way, other posters can easily work with your test data. Here's an example of what I mean:
CREATE TABLE #Table (custNo int, co_num int, order_class varchar(8))
INSERT INTO #Table( CustNo, co_num, order_class)
(SELECT 1000388,18290, 'QS'
UNION
SELECT 1000388,18292, 'QS'
UNION
SELECT 1000388,18297, 'MTO'
)
January 16, 2014 at 2:55 pm
Would it be better to do this with a Do While or a cursor?
No to both. Best is to use a set-based method, possibly like this one:
UPDATE tn
SET order_class = 'MTO'
FROM dbo.tablename tn
INNER JOIN (
SELECT custNo
FROM dbo.tablename
GROUP BY custNo
HAVING
MAX(CASE WHEN order_class = 'MTO' THEN 1 ELSE 0 END) = 1 AND
MAX(CASE WHEN order_class <> 'MTO' THEN 1 ELSE 0 END) = 1
) AS tn_MTO ON
tn_MTO.custNo = tn.custNo AND
tn.order_class <> 'MTO'
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 16, 2014 at 2:56 pm
Thank you very much Doug. I will try it first thing in the morning and will let you know. I really appreciate your help. Thanks.
Gillian
January 16, 2014 at 3:09 pm
Scott-
Would it be better to do it like this:
UPDATE T
SET order_class = 'MTO'
FROM #Table T
join #Table T2 ON t.custNo = T2.custNo and
t2.order_class = 'MTO' and
t.order_class != 'MTO'
or am I missing something here?
January 16, 2014 at 3:10 pm
Thanks so much for your response and help Scott.
I will try that too, but the #temp table that I need to update has already been grouped by Customer Number, and with your suggestion, I want to make sure that I don't update the records that should really be class =QS
If all of the customer's records have an QS class, those do not have to be updated at all. Only the same customer order records that have at least one class of QS, then all of those customer order records for that one customer order must have their class set to MTO.
Thanks again so much for your help.
Gillian
January 16, 2014 at 3:12 pm
Here's another set-based solution 😉
UPDATE t SET
order_class = 'MTO'
FROM #Table t
WHERE order_class <> 'MTO'
AND EXISTS( SELECT *
FROM #Table x
WHERE x.custNo = t.custNo
AND x.order_class = 'MTO')
January 16, 2014 at 3:22 pm
Gillian_Pappas2002 (1/16/2014)
...and with your suggestion, I want to make sure that I don't update the records that should really be class =QS...
Gillian,
Bit of advice. Whenever you're building an UPDATE or DELETE statement, first build the equivalent SELECT statement. That way you can check your results to make sure you aren't updating or deleting the wrong data:
So for example, if you wanted to check Luis's solution:
SELECT 'MTO' as [New Value], t.*
FROM #Table t
WHERE order_class <> 'MTO'
AND EXISTS( SELECT *
FROM #Table x
WHERE x.custNo = t.custNo
AND x.order_class = 'MTO')
This returns all rows that will be affected by Luis's UPDATE statement, and the first column here will show the value to which the field will be set.
Here I simply replaced the UPDATE and SET portions with a SELECT clause, and if you run that against a larger set of test data, you'll see that his solution works.
AFAIK, All three of the solutions given in this thread should yield an accurate solution.
January 16, 2014 at 4:02 pm
Gillian_Pappas2002 (1/16/2014)
If all of the customer's records have an QS class, those do not have to be updated at all. Only the same customer order records that have at least one class of QS, then all of those customer order records for that one customer order must have their class set to MTO.
Gillian
That's exactly what my code does.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 16, 2014 at 4:24 pm
Thank you so very much Luis.
Gillian
January 16, 2014 at 4:27 pm
Thank you so very much Doug. I will certainly do as you suggest from now one.
Thanks and have a wonderful evening.
Gillian
January 16, 2014 at 4:34 pm
Awesome SSS, you're great. Thanks so very, very much.
Gillian
January 16, 2014 at 4:37 pm
Thank you so very much Scott.
Gillian
January 16, 2014 at 4:38 pm
No worries.
Mine is more convoluted, I know, but for a large table could potentially perform much better.
As long as you got a solution you could use 🙂
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 16, 2014 at 5:00 pm
You're the MAN. I have to try it tomorrow. I can't connect from home to my work server. Waaaa. Hope you have an awesome evening and I can't thank you enough.
Gillian
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply