sql – 如何在不命名所有列的情况下合并两个表？

发布时间：2020-12-12 07:07:52 所属栏目：MsSql教程来源：网络整理

所以说我有两个具有相同列的表.用你的想象力让它们更大：

USER_COUNTERPARTY:
ID  |Name                        |Credit Rating    |Sovereign Risk    |Invoicing Type
----+----------------------------+-----------------+------------------+---------------
1   |Nat Bank of Transnistria    |7                |93                |Automatic
2   |Acme Ltd.                   |25               |12                |Automatic
3   |CowBInd LLP.                |49               |12                |Manual

TEMP:
ID  |Name                        |Credit Rating    |Sovereign Risk    |Invoicing Type
----+----------------------------+-----------------+------------------+---------------
2   |Acacacme Ltd.               |31               |12                |Manual
4   |Disenthralled Nimrod Corp.  |31               |52                |Automatic

我想将它们合并为一个,用第二个替换第一个具有相同ID的内容,并插入不存在的内容.我可以用这句话：

MERGE INTO USER_COUNTERPARTY C
USING TEMP T
ON (C.COUNTER_ID = T.COUNTER_ID)
WHEN MATCHED THEN UPDATE SET
    C.COUNTER_NAME = T.COUNTER_NAME,C.COUNTER_CREDIT = T.COUNTER_CREDIT,C.COUNTER_SVRN_RISK = T.COUNTER_SVRN_RISK,C.COUNTER_INVOICE_TYPE = T.COUNTER_INVOICE_TYPE
WHEN NOT MATCHED THEN INSERT VALUES (
    T.COUNTER_ID,T.COUNTER_NAME,T.COUNTER_CREDIT,T.COUNTER_SVRN_RISK,T.COUNTER_INVOICE_TYPE);

这还不错,但请注意我必须为每个列命名.有没有办法合并这些表而不必命名所有列？ Oracle documentation坚持我在合并中使用’insert’和’set’之后使用列名,因此可能需要一些其他语句.结果应该是这样的：

ID  |Name                        |Credit Rating    |Sovereign Risk    |Invoicing Type
----+----------------------------+-----------------+------------------+---------------
1   |Nat Bank of Transnistria    |7                |93                |Automatic
2   |Acacacme Ltd.               |31               |12                |Manual
3   |CowBInd LLP.                |49               |12                |Manual
4   |Disenthralled Nimrod Corp.  |31               |52                |Automatic

如果它有助于我在这里粘贴它：

CREATE TABLE USER_COUNTERPARTY
( COUNTER_ID             INTEGER       NOT NULL PRIMARY KEY,COUNTER_NAME           VARCHAR(38),COUNTER_CREDIT         INTEGER,COUNTER_SVRN_RISK      INTEGER,COUNTER_INVOICE_TYPE   VARCHAR(10) );

INSERT ALL
INTO USER_COUNTERPARTY VALUES (1,‘Nat Bank of Transnistria’,7,93,‘Automatic’)
INTO USER_COUNTERPARTY VALUES (2,‘Acme Ltd.’,25,12,‘Manual’)
INTO USER_COUNTERPARTY VALUES (3,‘CowBInd LLP.’,49,‘Manual’)
SELECT * FROM DUAL;

CREATE TABLE TEMP AS SELECT * FROM USER_COUNTERPARTY;
DELETE FROM TEMP;

INSERT ALL
INTO TEMP VALUES (2,‘Conoco Ltd.’,‘Automatic’)
INTO TEMP VALUES (4,‘Disenthralled Nimrod Corp.’,63,‘Manual’)
SELECT * FROM DUAL;

解决方法

我相信你必须避免使用列名的唯一选择是两个单独的语句：

delete from USER_COUNTERPARTY UC
      where exists
              (select null
                 from TEMP T
                where T.COUNTER_ID = UC.COUNTER_ID);

insert into USER_COUNTERPARTY UC
  select *
    from TEMP T
   where not exists
           (select null
              from USER_COUNTERPARTY UC
             where T.COUNTER_ID = UC.COUNTER_ID);

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!