oracle - merge multiple column values

I suggest that it would be better to create a VIEW than to modify the raw data. This avoids all risk that the update query is run twice etc. and the original data is always avaliable.

CREATE TABLE DAILY_DATA(DEPT INT,CLASS int, "DATE" DATE,BOOK_ID INT, TRANSFER_RETAIL DECIMAL(5,2),TRANSFER_COST DECIMAL(5,2));

INSERT INTO DAILY_DATA 
SELECT 100, 1,to_date('2022-04-10','yyyy-mm-dd'),100,23.4,54 FROM dual UNION ALL
SELECT 100, 1,to_date('2022-04-10','yyyy-mm-dd'), 200, 0, 92 FROM dual

CREATE VIEW TOTALLED AS
SELECT
  DEPT,
  CLASS,
  "DATE",
  BOOK_ID,
    CASE WHEN BOOK_ID = 100 THEN SUM(TRANSFER_RETAIL) OVER(partition BY "DATE")
      ELSE TRANSFER_RETAIL end TRANSFER_RETAIL,
    CASE WHEN BOOK_ID = 100 THEN SUM(TRANSFER_COST) OVER(partition BY "DATE") 
      ELSE TRANSFER_COST end TRANSFER_COST
from DAILY_DATA;

SELECT * FROM TOTALLED;

DEPT | CLASS | DATE      | BOOK_ID | TRANSFER_RETAIL | TRANSFER_COST
---: | ----: | :-------- | ------: | --------------: | ------------:
 100 |     1 | 10-APR-22 |     200 |               0 |            92
 100 |     1 | 10-APR-22 |     100 |            23.4 |           146

db<>fiddle here

You can use a MERGE statement to combine the rows:

MERGE INTO daily_data dst
USING (
  SELECT dept,
         class,
         "DATE",
         SUM(transfer_retail) AS transfer_retail,
         SUM(transfer_cost) AS transfer_cost
  FROM   daily_data
  WHERE  book_id = 200
  -- AND dept = 100
  -- AND class = 1
  -- AND "DATE" = DATE '2022-04-10'
  GROUP BY dept, class, "DATE"
) src
ON (    dst.book_id = 100
    AND dst.dept    = src.dept
    AND dst.class   = src.class
    AND dst."DATE"  = src."DATE")
WHEN MATCHED THEN
  UPDATE
  SET transfer_retail = dst.transfer_retail + src.transfer_retail,
      transfer_cost   = dst.transfer_cost + src.transfer_cost;

Which, for the sample data:

CREATE TABLE DAILY_DATA (DEPT, CLASS, "DATE", BOOK_ID, TRANSFER_RETAIL, TRANSFER_COST) AS
SELECT 100, 1, DATE '2022-04-10', 100, 23.4, 54 FROM DUAL UNION ALL
SELECT 100, 1, DATE '2022-04-10', 200, 0,    92 FROM DUAL;

Then after the MERGE, the table will contain:

DEPT	CLASS	DATE	BOOK_ID	TRANSFER_RETAIL	TRANSFER_COST
100	1	10-APR-22	100	23.4	146
100	1	10-APR-22	200	0	92

db<>fiddle here

Rather than merging them in an already existing column, just add a new virtual column such as

ALTER TABLE daily_data
ADD (
      transfer_total AS ( transfer_retail+transfer_cost )
    );

which won't occupy any extra space within the data segment, and just used to display the desired computed value always depending on those two columns. Btw, your current column's value of transfer_cost will be kept as orginal, and the normalisation rule for the table will be protected.