SQL how to insert from another table based on same row values?-CodePudding

I have two tables with similar column values for columns "name" and "regis". One table is already filled with values, the other has only values for "name". How do i get "regis" values from table1 into table2 based on same column values?

I tried something like this

INSERT INTO table2 (regis)
SELECT table1 (regis)
WHERE table2.name = table1.name

CodePudding user response：

I think, what you are after is insert when name not found in table2 or update if its found in both tables.

INSERT INTO TABLE2 (regis)
SELECT t1.REGIS FROM TABLE1 as t1
LEFT OUTER JOIN TABLE2 as t2 on t2.name = t1.name
WHERE t2.name is null

You can update table2 using the statement like below:

update t2
   set t2.regis = t1.regis
from table2 as t2
inner join table1 as t1 on t1.name = t2.name

If you are using SQL Server, then you can use merge statement to either insert or update in one step.

CodePudding user response：

Please try:

update table2 set regis = (select regis from table1 where table1.name=table2.name);

see the whole sample on: https://www.db-fiddle.com/f/3jC8PGeZZEuty3XVq8gdzz/9

CodePudding user response：

You need to use table2 as join in your select statement.

INSERT INTO table2 (regis)
SELECT table1.regis FROM Table1 INNER JOIN Table2 on table1.name = table2.name