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

In article <D6MLqE.Ix5_at_world.std.com>, sreedhar_at_world.std.com (sreedhar annamalai) wrote:

>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')
> WHERE T.A = 'c'
>
>3) Delete rows with replaced information--
>
>DELETE FROM Tbl
> WHERE A = 'a'
>
>
>Would appreciate any help.
>Thanks

you want at most one row with key 'c', right? in that case, i don't think your method is right. suppose there are two 'a' rows and no 'c' rows - you end up with two 'c' rows, i think.

what is the common case? what is your environment? in PL/SQL, i might use sql%rowcount and do something like:

begin
  update t set b = (select sum(b) from t where a in('a','c')) where a = 'c';

  if sql%rowcount = 0 then
    insert into t select 'c', sum(b) from t where a = 'a';   end if;

  delete from t where a = 'a';

end;

i don't think it can be done in fewer steps, but a different order may be more efficient depending on what cases are common.

take care: the above is not tested. Received on Mon Apr 10 1995 - 00:00:00 CEST