RE: Sql - updating multiple records with a sequential value

Ah that did it.
I really didn't expect to be able to accomplish this. Shows what I
know!

Thanks so much

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Elvis Budimlic
Sent: Wednesday, April 23, 2008 3:22 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: Sql - updating multiple records with a sequential value


You're almost there. What's throwing you off is that I have named the
ad-hoc 'ordering' column SUFFIX, instead of CMSUFFIX. So simply change
the b.cmsuffix to b.suffix and query should run. Alternatively, change
"AS suffix" to "AS cmsuffix". Either will work.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and
OS/400 www.centerfieldtechnology.com


-----Original Message-----
Subject: RE: Sql - updating multiple records with a sequential value

Subqueries are where I really struggle. I have taken your query and
converted to proper field names as such.
UPDATE contmast00 a SET a.cmsuffix =
CASE CMMASTF WHEN 'Y' THEN '00'
ELSE
(SELECT b.cmsuffix
FROM (SELECT cmmastr, cmcontract,
RIGHT(DIGITS(ROW_NUMBER()
OVER(PARTITION BY cmmastr
ORDER BY cmcontract)),2)
AS suffix
FROM contmast00 b
WHERE cmmastf = 'N') b
WHERE a.cmmastr = b.cmmastr AND a.cmcontract = b.cmcontract)
END

When I run this it tells me Column CMSUFFIX not in table B in *N. this
points to the (SELECT b.cmsuffix