Re: UPDATE statement with COUNT( ) as an ANALYTIC

From: <john.howard.65_at_gmail.com>
Date: Fri, 10 Aug 2007 04:19:58 -0700
Message-ID: <1186744798.918629.54810@i13g2000prf.googlegroups.com>

Got my answer (from Tom). Use the MERGE statement (see below). Still a bit miffed that the original UPDATE doesn't work.

MERGE INTO dropme d1
  USING ( SELECT ROWID r,

                 COUNT( * ) OVER( PARTITION BY object_type ) cnt
         FROM   dropme ) d2

  ON ( d1.ROWID = d2.r )
  WHEN MATCHED THEN
    UPDATE
       SET d1.n = d2.cnt
  WHEN NOT MATCHED THEN
    INSERT( n )
    VALUES( NULL ) Received on Fri Aug 10 2007 - 06:19:58 CDT