Do While or Cursor?

Question

Post reply

Do While or Cursor?

Gillian_Pappas2002

SSCommitted

Points: 1714
More actions
January 16, 2014 at 2:29 pm

#287380

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?
custNoco_numorder_class
100038818290QS
100038818292QS
100038818297MTO
Thanks very much.
Gillian

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Doug J. SSC Eights! Points: 832 More actions · Answer 1

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'

)

-Doug

http://xkcd.com/327/

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Gillian_Pappas2002 SSCommitted Points: 1714 More actions · Answer 3

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

Doug J. SSC Eights! Points: 832 More actions · Answer 4

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?

-Doug

http://xkcd.com/327/

Gillian_Pappas2002 SSCommitted Points: 1714 More actions · Answer 5

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

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')

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Doug J. SSC Eights! Points: 832 More actions · Answer 7

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.

-Doug

http://xkcd.com/327/

ScottPletcher SSC Guru Points: 101098 More actions · Answer 8

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Gillian_Pappas2002 SSCommitted Points: 1714 More actions · Answer 9

Gillian_Pappas2002

SSCommitted

Points: 1714

January 16, 2014 at 4:24 pm

#1680911

Thank you so very much Luis.

Gillian

Gillian_Pappas2002 SSCommitted Points: 1714 More actions · Answer 10

Thank you so very much Doug. I will certainly do as you suggest from now one.

Thanks and have a wonderful evening.

Gillian

Gillian_Pappas2002 SSCommitted Points: 1714 More actions · Answer 11

Awesome SSS, you're great. Thanks so very, very much.

Gillian

Gillian_Pappas2002 SSCommitted Points: 1714 More actions · Answer 12

Gillian_Pappas2002

SSCommitted

Points: 1714

January 16, 2014 at 4:37 pm

#1680914

Thank you so very much Scott.

Gillian

ScottPletcher SSC Guru Points: 101098 More actions · Answer 13

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Gillian_Pappas2002 SSCommitted Points: 1714 More actions · Answer 14

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