Can I use SQL instead of cursor?

Hi,

I'm currently converting a VB function to SQL-Server. The function uses a cursor to find the "terms of delivery" (TOD) with the highest priority.

I have a table with articlenumber, tod (and lots of other columns that doesn't matter now)

ABC123 , AFG
ABC123 , AFG
ABC123 , BGH
ABC123 , BGH
ABC123 , CDD

"CDD" has the highest priority and therefore ALL with the same articlenumber should use that tod.

The existing function uses a cursor and loops through a recordset and updates every row with the same articlenumber as the current row with the tod with the highest priority (of the ones read) with the same articlenumber.

One update per row takes "forever" to run...

I figured it would be possible to select the tod with the highest priority for one articlenumber into a temp table and then do ONE update to set the tod on all rows...Here is a quick and dirty. It could be improved with a little work:



create table foo (ArtNo varchar(25), pri varchar(25))
insert into foo values ('ABC123' , 'AFG')
insert into foo values ('ABC123' , 'AFG')
insert into foo values ('ABC123' , 'BGH')
insert into foo values ('ABC123' , 'BGH')
insert into foo values ('ABC123' , 'CDD')
insert into foo values ('ABCD123' , 'AFG')
insert into foo values ('ABCD123' , 'AFG')
insert into foo values ('ABCD123' , 'BGH')
insert into foo values ('ABCD123' , 'BGH')
insert into foo values ('ABCD123' , 'CDE')
select ArtNo, max(pri) pri
into #temp
from foo
group by ArtNo
update foo
set foo.pri = #temp.pri
from foo,#temp
where foo.Artno = #temp.ArtNo

select * from foo
drop table foo|||Hmm..

What if BGH has the highest priority?

The priority is not determined by the alphabet. :-(|||Then you will need a table or some other method to determine an ordered set of priorities.

As a start, look at how you do it now.|||I'm at home now, but something struck me on the way home...

If I change the three letter combinations to numbers according to their priority, I can then use:

SELECT articlenumber, MAX(tod)
FROM mytable
GROUP BY articlenumber

into a temptable..

The I use the temptable and updates ALL articlenumbers with their right tod.

I'll try it tomorrow. Clock is 5 in the afternoon in sweden now. :-)|||I'm at home now, but something struck me on the way home...

If I change the three letter combinations to numbers according to their priority, I can then use:

SELECT articlenumber, MAX(tod)
FROM mytable
GROUP BY articlenumber

into a temptable..

Then I use the temptable and updates ALL articlenumbers with their right tod.

I'll try it tomorrow. Clock is 5 in the afternoon in sweden now. :-)|||I'm at home now, but something struck me on the way home...

If I change the three letter combinations to numbers according to their priority, I can then use:

SELECT articlenumber, MAX(tod)
FROM mytable
GROUP BY articlenumber

into a temptable..

Then I use the temptable and updates ALL articlenumbers with their right tod.

I'll try it tomorrow. Clock is 5 in the afternoon in sweden now. :-)|||Worked like a charm.. My solution:

--

/* Get priorities of tod */
UPDATE mytable
SET tod =
CASE tod
WHEN 'BGH' THEN '1'
WHEN 'AFG' THEN '2'
WHEN 'CDD' THEN '3'
ELSE '999'
END

/* Create temptable */
create table dbo.temp_table (articlenumber varchar(31), PRIO varchar(10))

/* Make temporary list with "highest" TOD */
insert into dbo.temp_table (articlenumber, PRIO)
select articlenumber,
CASE MIN(cast(tod AS INT))
WHEN '1' THEN 'BGH'
WHEN '2' THEN 'AFG'
WHEN '3' THEN 'CDD'
ELSE 'N/A'
END
from mytable
GROUP BY articlenumber

/* Update ALL to highest tod */
UPDATE mytable
SET tod = b.PRIO
FROM mytable a, dbo.temp_table b
WHERE
(a.articlenumber = b.articlenumber)

--