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.