asked on

sql table merge

I have two tables with the values:

Table 1:

a b c
n - 1
m - 3
o - 4

Table 2:

a b c
n 1 -
m 2 -

I want to create table 3, based on the identity of the string in column "a" between tables 1 and 2
and not omitting any rows of Table 1:

Table 3:

a b c
n 1 1
m 2 3
o - 4

What sql will do this?

PortletPaul

I think this may be what you are looking for:

select  /* INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] new_table */

        t1."a"
      , coalesce(t1."b",t2."b") "b"
      , coalesce(t1."c",t2."c") "c"
from Table1 t1
left join Table2 t2 on t1."a" = t2."a"
;

Open in new window

but this also matches the expected result above. (i.e. it isn't clear if you want Table1.b to overrule Table2.b and so on)

select  /* INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] new_table */

        t1."a"
      , t2."b"
      , t1."c"
from Table1 t1
left join Table2 t2 on t1."a" = t2."a"
;

Open in new window

Regarding the select into I'll leave that to you, see:
http://www.postgresql.org/docs/9.1/static/sql-selectinto.html

**PostgreSQL 9.3.1 Schema Setup**:

    
    
    CREATE TABLE Table1
    	("a" varchar(1), "b" int, "c" int)
    ;
    	
    INSERT INTO Table1
    	("a", "b", "c")
    VALUES
    	('n', null, 1),
    	('m', null, 3),
    	('o', null, 4)
    ;
    
    CREATE TABLE Table2
    	("a" varchar(1), "b" int, "c" int)
    ;
    	
    INSERT INTO Table2
    	("a", "b", "c")
    VALUES
    	('n', 1, null),
    	('m', 2, null)
    ;

**Query 1**:

    select  /* INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] new_table */
    
            t1."a"
          , coalesce(t1."b",t2."b") "b"
          , coalesce(t1."c",t2."c") "c"
    from Table1 t1
    left join Table2 t2 on t1."a" = t2."a"
    

**[Results][2]**:
    
    | A |      B | C |
    |---|--------|---|
    | m |      2 | 3 |
    | n |      1 | 1 |
    | o | (null) | 4 |


**Query 2**:

    select  /* INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] new_table */
    
            t1."a"
          , t2."b"
          , t1."c"
    from Table1 t1
    left join Table2 t2 on t1."a" = t2."a"
    

**[Results][3]**:
    
    | A |      B | C |
    |---|--------|---|
    | m |      2 | 3 |
    | n |      1 | 1 |
    | o | (null) | 4 |



  [1]: http://sqlfiddle.com/#!15/25921/4

Open in new window

earth man2

Do you want to omit non matching rows in table2 ? If not then use FULL OUTER JOIN instead.

awking00

Is it possible to have values like?
Table1
n 5 1
m - 3
o - 4
Table2
n 1 -
m 2 -
If so, what would be the expected results for table3?

code4

ASKER

Table 1 never has values in column 2.

I want these values to be filled from the matches in table 2,
and if none are found in table 2, to be left unfilled.
Table 3 should have the same number of rows as table 1.

Thanks

awking00

Then PortletPaul's solution should work.

code4

ASKER

PortletPaul: Thanks, but this did not work.
It created duplicate entries in table 3.

I need table 3 to have exactly the same number of rows as table 1.

PortletPaul

your sample data does not demonstrate this issue,
could you provide more details please?

all we know about your tables is what you put in the question

earth man2

SELECT DISTINCT ...

earth man2

I guess if same number of rows is essential then use a correlated subquery.

PortletPaul

we might use distinct or maybe a correlated query: who really knows until we see more information

my crystal ball fell off the table and cracked...

ASKER CERTIFIED SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial