Insert records from two tables that match-CodePudding

I have the following tables:

CREATE TABLE forms 
(
    ID INT NOT NULL,
    NAME TEXT NOT NULL,
    TITLE TEXT NOT NULL
);

CREATE TABLE new_forms 
(
    ID INT NOT NULL,
    NAME TEXT NULL,
    TITLE TEXT NULL
);

INSERT INTO forms VALUES (0, 'test', 'test');
INSERT INTO new_forms VALUES (0, 'new_test', NULL);

And I'm using the following query:

INSERT INTO forms(id, name, title) 
    SELECT
        1, COALESCE(nf.name, f.name), COALESCE(nf.title, f.title) 
    FROM
        forms f 
    LEFT OUTER JOIN
        new_forms nf ON nf.id = f.id;

SELECT * FROM forms;

The idea is to add both rows that match to the table.

In this example this two new records should be added:

1   test        test
1   new_test    test

But it's only adding the last one.

I have tried with all the join and none of them worked.

Fiddle

Thanks

CodePudding user response：

You are using a join in the query which will give you only 1 row. If you need 2 rows. You have to use UNION ALL clause -

INSERT INTO forms(id, name, title) 
    SELECT
        1, COALESCE(nf.name, f.name), COALESCE(nf.title, f.title) 
    FROM
        forms f 
    LEFT OUTER JOIN
        new_forms nf ON nf.id = f.id
    UNION ALL
    SELECT
        1, COALESCE(f.name, nf.name), COALESCE(nf.title, f.title) 
    FROM
        forms f 
    LEFT OUTER JOIN
        new_forms nf ON nf.id = f.id;