Update Duplicate rows

[vijayk]

Hi

I have a table like the one below

Pkey Sno
-----------------
AA 0
BB 0
CC 0
AA 0
BB 0
BB 0

and I want to update the duplicate rows like this

Pkey Sno
-----------------
AA 1
BB 1
CC 0
AA 2
BB 2
BB 3

Please suggest a way...

thanks

[Knut Stolze]

vijayk wrote:

Hi

I have a table like the one below

Pkey Sno
-----------------
AA 0
BB 0
CC 0
AA 0
BB 0
BB 0

and I want to update the duplicate rows like this

Pkey Sno
-----------------
AA 1
BB 1
CC 0
AA 2
BB 2
BB 3

Please suggest a way...

UPDATE t AS o
SET sno = ROWNUMBER() OVER ( PARTITION BY pkey )
WHERE ( SELECT COUNT(*)
FROM t AS i
WHERE o.a = i.a ) > 0

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[vijayk]

Hi
I am getting

Pkey Sno
-----------------
AA 1
BB 1
CC 1 --instead of 0
AA 2
BB 2
BB 3

[Knut Stolze]

vijayk wrote:

Hi
I am getting

Pkey Sno
-----------------
AA 1
BB 1
CC 1 --instead of 0
AA 2
BB 2
BB 3

Sorry... I copied the wrong query. Make the comparison in the WHERE clause
a "WHERE ( SELECT ... ) > 1".

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Knut Stolze]

Knut Stolze wrote:

vijayk wrote:

Hi
I am getting

Pkey Sno
-----------------
AA 1
BB 1
CC 1 --instead of 0
AA 2
BB 2
BB 3

Sorry... I copied the wrong query. Make the comparison in the WHERE
clause a "WHERE ( SELECT ... ) > 1".

p.s: I hope you do understand what this statement is doing. Because your
question indicates that you just tried it as I posted it.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Eugene F]

Given your table is t(pkey, sno):

UPDATE
(SELECT sno, new_sno FROM (
SELECT sno,
ROW_NUMBER() OVER(PARTITION BY pkey) new_sno,
COUNT(*) OVER(PARTITION BY pkey) cnt
FROM t
) a WHERE cnt > 1
) b
SET b.sno = b.new_sno
-Eugene

[Serge Rielau]

Eugene F wrote:

Given your table is t(pkey, sno):

UPDATE
(SELECT sno, new_sno FROM (
SELECT sno,
ROW_NUMBER() OVER(PARTITION BY pkey) new_sno,
COUNT(*) OVER(PARTITION BY pkey) cnt
FROM t
) a WHERE cnt > 1
) b
SET b.sno = b.new_sno

:-)
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[Eugene F]

Serge, are you smiling having found my solution funny or may be silly?
But I actually tested it worked.
Oh, I see ... LUW vs. iSeries from an earlier topic makes you
smiling... right?

:-)

-Eugene

[Serge Rielau]

Eugene F wrote:

Serge, are you smiling having found my solution funny or may be silly?
But I actually tested it worked.
Oh, I see ... LUW vs. iSeries from an earlier topic makes you
smiling... right?

:-)

I had a ton of folks drafted to get this and the other SQL features
added in DB2 V8.1 FP4 for LUW working in time for the TPC-C effort.
Posts like this one help validate my belief that what was added was all
good stuff, well worth the investment and that TPC-C is more
representative than many people give it cridit.

Call me a proud father if you wish :-)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[Eugene F]

You got it!
:-))

-Eugene