Need SQL help: replace multiple rows in table with single row

Would like to know if anyone can help me with the following problem.

I need to replace multiple rows in a table with single rows (while aggregating another column) as follows --

Original state of table Tbl:
  A B
---- -----

  a 1
  a 2
  b 1
  c 1 <-- this row with A='c' may or may not exist

Operation: replace rows where A='a' with a single row with A='c' and

           adding corresponding values in column B.

Result:
  A B
---- -----

  b 1
  c 4 <== 1 + 2 + 1

This is what I am doing now and feel there must be a better way:

1. First make sure a row with A='c' exists--

UPDATE Tbl

   SET A = 'c'
   WHERE A = 'a'

     AND (NOT EXISTS (SELECT T1.A
                      FROM Tbl T1
                      WHERE T1.A = 'c'))

2) Aggregate column B information--

UPDATE Tbl T

   SET T.B = (SELECT SUM(T1.B)

              FROM Tbl T1
              WHERE T1.A = 'a'
                 OR T1.A = 'c')

3) Delete rows with replaced information--

DELETE FROM Tbl

   WHERE A = 'a'

Would appreciate any help.
Thanks

Sreedhar Annamalai
Sagitta Software, Inc.
sreedhar_at_world.std.com Received on Thu Apr 06 1995 - 00:00:00 CEST