Update Duplicate rows

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

vijayk - 17 Jan 2006 16:44 GMT

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

Reply to this Message

Knut Stolze - 17 Jan 2006 17:01 GMT

> Hi
>
[quoted text clipped - 21 lines]
>
> 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

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

vijayk - 17 Jan 2006 17:34 GMT

Hi
I am getting

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

Reply to this Message

Knut Stolze - 17 Jan 2006 17:41 GMT

> Hi
> I am getting
[quoted text clipped - 7 lines]
> BB 2
> BB 3

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

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

Knut Stolze - 17 Jan 2006 17:44 GMT

>> Hi
>> I am getting
[quoted text clipped - 10 lines]
> 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.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

Eugene F - 17 Jan 2006 17:47 GMT

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

Reply to this Message

Serge Rielau - 17 Jan 2006 22:26 GMT

> Given your table is t(pkey, sno):
>
[quoted text clipped - 8 lines]
> SET b.sno = b.new_sno
:-)

Signature

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

Reply to this Message

Eugene F - 17 Jan 2006 23:04 GMT

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

Reply to this Message

Serge Rielau - 18 Jan 2006 10:57 GMT

> 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

Signature

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

Reply to this Message

Eugene F - 18 Jan 2006 15:41 GMT

You got it!

:-))

-Eugene

Reply to this Message