Re: Better way to do this Corelated Update ?

That where clause looks pointless. Did you mean to have "and flag != 'N'"? Even if you did, why not just "where flag != 'N'" without that 'where exists' nonsense? Not that it makes any difference, but you don't need the 'group by' clause either.

Richard

R Chin wrote:
>
> Want to update the SALE_CT column
> to the count of C1 grouped by C1
> based on value of C2......
> so from this......
>
> TEST_T
> ---------------------------
> ID FLAG SALE_CT
> --- ------- --------------
> 111 Y
> 111 Y
> 111 Y
> 112 Y
> 113 Y
> 113 Y
> 114 Y
> 114 Y
> 114 N
> 114 Y
> 114 Y
>
> To like this.......
>
> ID FLAG SALE_CT
> --- --- ----------
> 111 Y 3
> 111 Y 3
> 111 Y 3
> 112 Y 1
> 113 Y 2
> 113 Y 2
> 114 Y 4
> 114 Y 4
> 114 N 4
> 114 Y 4
> 114 Y 4
>
> --This DML seems to work...
>
> update TEST_T
> set SALE_CT = (select count(*)
> from TEST_T b
> where b.ID = TEST_T.ID
> and b.FLAG != 'N'
> group by b.ID)
> where exists
> (select 'x' from TEST_T b
> where b.ID = TEST_T.ID);
>
> Do you foresee any gotchas with this DML ?
> I appreciate if you can suggest another way to do this.
>
> Thanks
> robert
Received on Mon Apr 22 2002 - 17:47:19 CDT