CODEWITHSUNDEEP
sqlsql-serversql-insert
Christopher Tsoi
Christopher Tsoi

Reputation: 63

How to do INSERT INTO updated data from another table?

I have a new table (called "NewTable") which is created with the data from 2 tables (t1 and t2). I am trying to INSERT any new data in t1 and t2 INTO "NewTable". Please see the example below.

NewTable:

id | col1 | col2 | col3 | col4 |

t1:

id | col1 | col2 | col5 | col6 |

t2:

id | col3 | col4 | col7 | col8 |

my script is:

INSERT NewTable (id, col1, col2, col3, col4)
SELECT t1.d1, col1, col2, col3, col4
FROM NewTable left join t1 on NewTable.id = t1.id left join t2 on t1.id=t2.id
WHERE t1.id is NULL;

I got this error message

Cannot insert the value NULL into column 'id', table 'New Table'; column does not allow nulls. INSERT fails.

I feel like my script is off. Should I use right join instead of left join or should I put "WHERE NewTable is NULL" instead?

Thank you for your help

Upvotes: 0

Views: 57

Answers (1)

forpas
forpas

Reputation: 164089

If you want to insert into NewTable rows from t1 and t2 with the same id that don't already exist in NewTable then you must join with an INNER join t1 and t2, then LEFT join NewTable and return the non matching rows:

INSERT INTO NewTable (id, col1, col2, col3, col4)
SELECT t1.id, t1.col1, t1.col2, t2.col3, t2.col4
FROM t1 INNER JOIN t2 ON t2.id = t1.id
LEFT JOIN NewTable ON NewTable.id = t1.id
WHERE NewTable.id IS NULL;

Upvotes: 1

Related Questions