How to multi-update the table in Oracle?-CodePudding

I have these tables called transaction_line_fact and channel_dim:

SQL> desc transaction_line_fact
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 TRANSACTION_ID                                                             NUMBER(20)
 TRANSACTION_LINE_ID                                                        NUMBER(20)
 TRANID                                                                     VARCHAR2(30)
 TRANSACTION_TYPE                                                           VARCHAR2(50)
 TRANDATE                                                                   DATE
 KPI_CHANNEL_SKEY                                                           NUMBER(20)
 KPI_LOCATION_SKEY                                                          NUMBER(20)
 KPI_DEPARTMENT_SKEY                                                        NUMBER(20)
 KPI_ITEM_SKEY                                                              NUMBER(20)
 AMOUNT                                                                     NUMBER(8,2)
 COST                                                                       NUMBER(8,2)
 UNITS                                                                      NUMBER(5)
 KPI_DW_SKEY                                                       NOT NULL NUMBER(20)

SQL> desc channel_dim
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 DATE_CREATED                                                               DATE
 IS_RECORD_INACTIVE                                                         CHAR(1)
 LAST_MODIFIED_DATE                                                         DATE
 LIST_ID                                                                    NUMBER(20)
 LIST_ITEM_NAME                                                             VARCHAR2(20)
 KPI_DW_SKEY                                                       NOT NULL NUMBER(20)
 KPI_DW_INSERT_DATE                                                         DATE
 KPI_DW_UPDATE_DATE                                                         DATE

Currently the in transaction_line_fact table KPI_CHANNEL_SKEY contains null values but I want to populate it with the help of KPI_DW_SKEY column in channel_dim.

Also I've tried doing this way:

SQL> update transaction_line_fact set kpi_channel_skey = (select c.kpi_dw_skey from channel_dim c join transaction_line_fact t on c.kpi_dw_skey=t.kpi_dw_skey);
update transaction_line_fact set kpi_channel_skey = (select c.kpi_dw_skey from channel_dim c join transaction_line_fact t on c.kpi_dw_skey=t.kpi_dw_skey)
                                                     *
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row


SQL> update transaction_line_fact set kpi_channel_skey = (select kpi_dw_skey from channel_dim) where kpi_channel_skey is null;
update transaction_line_fact set kpi_channel_skey = (select kpi_dw_skey from channel_dim) where kpi_channel_skey is null
                                                     *
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row


SQL> update transaction_line_fact set kpi_channel_skey in (select kpi_dw_skey from channel_dim) where kpi_channel_skey is null;
update transaction_line_fact set kpi_channel_skey in (select kpi_dw_skey from channel_dim) where kpi_channel_skey is null
                                                  *
ERROR at line 1:
ORA-00927: missing equal sign

But again it shows error like ORA-01427: single-row subquery returns more than one row

Any idea on How to populate it? Thank You so much!

CodePudding user response：

Correct syntax would be

UPDATE transaction_line_fact f
   SET f.kpi_channel_skey =
          (SELECT c.kpi_dw_skey
             FROM channel_dim c
            WHERE c.kpi_dw_skey = f.kpi_dw_skey);

In other words, you have to join channel_dim with transaction_line_fact, but not specifying that table once again in a subquery, but using a where clause to join appropriate columns.

Although this is now "OK" as far as the principle is concerned, I can't tell whether it will (or will not) raise an error; too_many_rows could happen again, but - in that case - you have to decide what to do if for the same kpi_dw_skey channel_dim contains more than a single row - will you take only one (which one?), or ... Really, can't tell.