在两个表之间交换两个列的值 [英] Swap two columns values between two tables

问题描述

我有两个表NAVEEN_T1和NAVEEN_T2，它们具有Id和Name之类的列.

I have two tables NAVEEN_T1 and NAVEEN_T2 having columns like Id and Name.

如何基于ID交换两个表的名称列值?

How to swap name column values of both the tables on the basis of Id?

 

SQL> SELECT * FROM NAVEEN_T1;

ID         NAME
---------- ----------
1          GOLDI
2          NAVEEN
3          AMIT

SQL> SELECT * FROM NAVEEN_T2;

ID         NAME
---------- ----------
1          RANJAN
2          SOM
3          ABHAY

我想要类似的输出

SQL> SELECT * FROM NAVEEN_T1;

ID         NAME
---------- ----------
1          RANJAN
2          SOM
3          ABHAY

SELECT * FROM NAVEEN_T2;

ID         NAME
---------- ----------
1          GOLDI
2          NAVEEN
3          AMIT

谢谢.

推荐答案

现在，这可能被简化了，但是，嘿-这就是您的示例数据所建议的.这个想法是:交换表名，而不是数据.看看:

Now, this might be oversimplified, but hey - that's what your sample data suggest. The idea is: exchange table names, not data. Have a look:

SQL> create table naveen_t1 (id number, name varchar2(20));

Table created.

SQL> create table naveen_t2 (id number, name varchar2(20));

Table created.

SQL> insert all
  2    into naveen_t1 values (1, 'GOLDI')
  3    into naveen_t1 values (2, 'NAVEEN')
  4    into naveen_t1 values (3, 'AMIT')
  5    --
  6    into naveen_t2 values (1, 'RANJAN')
  7    into naveen_t2 values (2, 'SOM')
  8    into naveen_t2 values (3, 'ABHAY')
  9  select * from dual;

6 rows created.

SQL> select * From naveen_t1;

        ID NAME
---------- --------------------
         1 GOLDI
         2 NAVEEN
         3 AMIT

SQL> select * From naveen_t2;

        ID NAME
---------- --------------------
         1 RANJAN
         2 SOM
         3 ABHAY

这是技巧 :

SQL> rename naveen_t2 to temp;

Table renamed.

SQL> rename naveen_t1 to naveen_t2;

Table renamed.

SQL> rename temp to naveen_t1;

Table renamed.

SQL> select * from naveen_t1;

        ID NAME
---------- --------------------
         1 RANJAN
         2 SOM
         3 ABHAY

SQL> select * From naveen_t2;

        ID NAME
---------- --------------------
         1 GOLDI
         2 NAVEEN
         3 AMIT

SQL>

这篇关于在两个表之间交换两个列的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！