Oracle中如何多次更新表？

我有這些名為 transaction_line_fact 和 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

目前，transaction_line_fact 表中KPI_CHANNEL_SKEY包含空值，但我想借助KPI_DW_SKEYchannel_dim 中的列來填充它。

我也試過這樣做：

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

但它再次顯示錯誤 ORA-01427: single-row subquery returns more than one row

關于如何填充它的任何想法？太感謝了！

uj5u.com熱心網友回復：

正確的語法是

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);

換句話說，您必須連接channel_dimwith transaction_line_fact，但不要在子查詢中再次指定該表，而是使用where子句連接適當的列。

雖然現在就原則而言這是“OK”的，但我不知道它是否會（或不會）引發錯誤；too_many_rows可能會再次發生，但是 - 在這種情況下 - 如果同一行kpi_dw_skey channel_dim包含多于一行，你必須決定該怎么做- 你會只取一個（哪一個？），或者......真的，不能說。

標籤：甲骨文 合并 插入

上一篇：如何消除空值并改為顯示值

下一篇：如何在本地機器上使用kubectl運行遠程鏡像（OracleContainerImage）？