Better way to do this Corelated Update ?

From: R Chin <rchin_at_panix.com>
Date: Mon, 22 Apr 2002 14:16:47 -0400
Message-ID: <aa1k04$t58$1@reader1.panix.com>

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 - 13:16:47 CDT