Swap two columns values between two tables

问题:

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

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

I want output like:

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

Thanks in advance.

回答1:

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

Here goes the trick:

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>

回答2:

You can try this

update table1 t1
set    t1.col1 = (select t2.col2
                  from   table2 t2
                  where  t2.id = t1.id
                  and    t1.col1 < t1.col2)

Something like that should do it easily.

The only tricky point I see is matching the row from table2 to the row from table1. In my example, I supposed both tables share an unique "id" column which enables easy matching. Modify the query with something more appropriate.

回答3:

CREATE TABLE naveen_t1
    (`id` int, `name` varchar(6))
;

INSERT INTO naveen_t1
    (`id`, `name`)
VALUES
    (1, 'GOLDI'),
    (2, 'NAVEEN'),
    (3, 'AMIT')
;


CREATE TABLE naveen_t2
    (`id` int, `name` varchar(6))
;

INSERT INTO naveen_t2
    (`id`, `name`)
VALUES
    (1, 'RANJAN'),
    (2, 'SOM'),
    (3, 'ABHAY')
;
create  table naveen_t1_b 
as 
select * from naveen_t1;
create  table naveen_t2_b 
as 
select * from naveen_t2;

update naveen_t1 ta set  ta.name=(select tb.name
                                  from naveen_t2_b tb where tb.id=ta.id);
update naveen_t2 ta set  ta.name=(select tb.name
                                  from naveen_t1_b tb where tb.id=ta.id);
select * from naveen_t1;
select * from naveen_t2;

Hope this will solve your problem ,i have created backup tables to capture the values. You can use temporary tables instead of permanent tables

Check here-http://sqlfiddle.com/#!9/fdc52e/2