CODEWITHSUNDEEP
sqloracle-database
user168574
user168574

Reputation: 15

CREATE New Table from existing tables in Oracle 11g

I have 2 tables that doesn't have primary keys. These 2 tables have same number of rows. I want to create a new table from getting some columns from table1 and some columns from table 2. I want to combine first row from table1 and first row from table2. Below is example

TABLE1

ACOL1 ACOL2 ACOL3
A1 A2 A3
B1 B2 B3
C1 C2 C3

TABLE2

BCOL1 BCOL2 BCOL3
11 12 13
21 22 23
31 32 33

COMBINED_TABLE

ACOL1 BCOL2 BCOL3
A1 12 13
B1 22 23
C1 32 33

I tried below query but no luck. It gives below error:
Query : 

create table COMBINED_TABLE 
AS 
select a.ACOL1, b.BCOL2, b.BCOL3 
from (select ACOL1,rownum from TABLE1) a, 
     (select BCOL2, BCOL3, rownum from TABLE2) b 
WHERE a.rownum = b.rownum

Error : ORA-01747:"invalid user.table.column, table.column, or column specification"

Upvotes: 0

Views: 6061

Answers (3)

user330315
user330315

Reputation: 

create table combined_table
as
select a.acol1, b.bcol2, b.bcol3
from (
  select acol1, row_number() over (order by acol1) as rn
  from table1
) a 
  join (
    select bcol2, bcol3, row_number() over (order by bcol1) as rn
    from table2
  ) b on a.rn = b.rn

Using row_number() is more robust than rownum as you can actually define what "last" or "first" row means (those terms don't have a meaning unless some order is defined).

When you define an order by in the window function the resulting join is more stable as the row numbers are always calculated the same way (which is not the case with rownum).

Upvotes: 1

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

Try this:

CREATE TABLE table1a (
  acol1 NUMBER,
  acol2 NUMBER,
  acol3 NUMBER
);

CREATE TABLE table2a (
  bcol1 NUMBER,
  bcol2 NUMBER,
  bcol3 NUMBER
);

INSERT INTO table1a VALUES (1, 2, 3);
INSERT INTO table1a VALUES (4, 5, 6);

INSERT INTO table2a VALUES (10, 20, 30);
INSERT INTO table2a VALUES (40, 50, 60);

CREATE TABLE combined_table (ct1, ct2, ct3) AS
  SELECT a.acol1, b.bcol2, b.bcol3
    FROM
      (SELECT a.*, rownum AS rn FROM table1a a) a,
      (SELECT b.*, rownum AS rn FROM table2a b) b
  WHERE a.rn = b.rn
;

Upvotes: 0

Maxim Balaganskiy
Maxim Balaganskiy

Reputation: 1574

You cannot use rownum as a column name, giving it an alias solves the problem

create table COMBINED_TABLE 
AS 
select a.ACOL1, b.BCOL2, b.BCOL3 
from (select ACOL1,rownum rn from TABLE1) a, 
     (select BCOL2, BCOL3, rownum rn from TABLE2) b 
WHERE a.rn = b.rn

Upvotes: 0

Related Questions