0

Friends, i have database named as DB1 with a table called TB1A and another database as DB2 with a table TB2A.

TB1A having 5 columns and TB2A having 8 columns, All the columns available in TB1A is present in TB2A.

I want to create new table called TB3A which should be a merged result of a TB1A and TB2A. Means TB3A will have 8 columns and contains all the datas of TB1A and TB2A.

CREATE TABLE TB3A AS (

SELECT * 
  FROM DB1.TB1A 
  JOIN  DB2.TB2A 
) 

While i trying like above it is showing an error like below

Error Code: 1060

Duplicate column name 'Agreement_Id'

Execution Time : 0 sec

Transfer Time : 0 sec

Total Time : 0 sec

Does any one have any idea about this. I have google it this issue but i am not able to find the solution for this ..

  • What part of the error message seems unclear? Obviously you cannot have two columns with the same name in a table. – mustaccio Jul 27 '17 at 14:11
  • 1
    Do you want 13 columns? Do the tables have the same number of rows? Is that how many rows you should have in the result? We need some understanding of what you mean by "merge" -- to decide whether to recommend CROSS JOIN (as already mentioned in one anwser) versus JOIN..ON versus UNION. – Rick James Jul 27 '17 at 22:54
  • @RickJames i just want to merge the two tables which may have some columns with same name and different in no of columns. Ie the result will be another table. – Pranav MS Jul 28 '17 at 4:11
  • So, the total number of ROWs will be the sum of the number of rows in the existing tables? Sorry to keep asking question, but your specification is still imprecise. – Rick James Jul 28 '17 at 4:13
  • @RickJames yes. the total no of columns will be the merged sum of two tables. For ex. if TB1A have 5 col,TB2A have 8 columns but 3 columns are common i the two table table so the total no of column in the resultant table will be.10( 5(from TB1A)+5(from TB2A)). I thin you got my idea. – Pranav MS Jul 28 '17 at 4:26
2

What you've entered is equivalent to:

CREATE TABLE TB3A AS (

SELECT a.AgreementID, a.Column2, a.Column3, a.Column4,
       a.Column5, b.AgreementID, b.Column2, b.Column3,
       b.Column4, b.Column5, b.Column6, b.Column7, b.Column8
  FROM TB1A a JOIN TB2A b
);

You'd have 13 columns, with only 8 distinct names. This is a problem.

What you probably want to do is this:

CREATE TABLE TB3A AS (

SELECT AgreementID, Column2, Column3, Column4,
       Column5, Column6, Column7, Column8
  FROM TB2A
);

INSERT INTO TB3A (AgreementID, Column2, Column3, Column4, Column5)
SELECT * FROM TB1A;

TB3A will now have m + n rows, where m is the number of rows in TB1A, and n is the number of rows in TB2A.

If some rows in TB1A and TB2A are duplicates, this will not remove those. You can change the INSERT ... SELECT statement to filter out rows from TB1A that match rows already in TB3A (thus, originally from TB2A) with matching values in whatever columns you need. For instance, to not bring in rows from TB1A that have the same AgreementId as rows already in TB3A, the INSERT would be:

INSERT INTO TB3A (AgreementID, Column2, Column3, Column4, Column5)
SELECT * FROM TB1A 
 WHERE NOT EXISTS (SELECT 1 FROM TB3A
                    WHERE TB1A.AgreementID = TB3A.AgreementId
                  )
;

Additional note on the original statement: JOIN is generally assumed to be an INNER JOIN - one that matches rows form the two tables based on one or more criteria. For example, TB1A JOIN TB2A ON TB1A.AgreementId = TB2A.AgreementId would return the rows from TB1A and TB2A the had the same AgreementID. Rows in TB1A with an AgreementID not found in TB2A would not be returned, and rows in TB2A with an AgreementID not found in TB1A would not be returned.

If there were three rows in TB1A with the same AgreementId, and 2 rows in TB2A with that same ID, those rows would generate 6 rows in the result set:

TB1A      TB2A
  x         m
  y         n
  z

Result set:
    x,m
    x,n
    y,m
    y,n
    z,m
    z,n

In some SQL dialects (MySQL included), if there is no JOIN criteria specified, then the INNER JOIN becomes what's called a CROSS JOIN. Here, since there's nothing to indicate which rows should not be joined together, all rows are joined together. If you have m rows in TB1A, and n rows in TB2A, you don't get m+n rows - you get m*n rows. That's what would have happened, if your original statement had run.

  • I guess that the OP wants something else and not a join. Perhaps a UNION, not sure. – ypercubeᵀᴹ Jul 27 '17 at 17:32
  • @ypercubeᵀᴹ please see the comments below thw questions. This will clear you. Thnaks – Pranav MS Jul 28 '17 at 4:27
  • 1
    @PranavMS it's still not clear if you want a UNION or some kind of JOIN. It would be better if you edited your question and added sample data and wanted result. – ypercubeᵀᴹ Jul 28 '17 at 6:27
0

Please try by providing alias or db.table as below.

CREATE TABLE TB3A AS (
  SELECT A.coli, A.col2, A.col3, A.col4, A.col5    
  FROM DB1.TB1A A    
  JOIN  DB2.TB2A B)

Not the answer you're looking for? Browse other questions tagged or ask your own question.