insert data to column based on query

Question

I have two tables. The table I am trying to update is table A which doesn't have some IDs ProfileIDs. At the other hand table B has all the IDs. I am trying to update the column in Table A based on a query. When I join the tables I get all the results in my query and as i try to update/insert the results in my column I get a cannot insert into my table. I am intending to insert my results with the following query

 insert into TableA a
(profileID) 

select b.profileID 
from tableA 
join TableB B on B.userID = A.userID  

where a.profileID = '' or a.profileID is null  


    Table A                                                            Table B

UserID | profileID | lastname | Firstname               UserID | profileID | lastname | Firstname 
0012356               Wung      Clara                   0012356   15963         Wung      Clara 
0512356    14753      Williams  Michael                 0512356   14753      Williams     Michael 
0956356               Walters   Jeff                    0956356   94356      Walters      Jeff 
0486312    68956      Wwagner   George                  0486312   68956      Wwagner   George 


select A.*, B.profileID 
from TableA A
join TableB B on B.userID = A.userID 


Results 



UserID | profileID | lastname | Firstname 
0012356   15963         Wung      Clara 
0512356   14753      Williams     Michael 
0956356   94356      Walters      Jeff 
0486312   68956      Wwagner   George

Which RDBMS are you using? Are you wanting to UPDATE tableA to have the profileIDs? Your results table isn't accurate. A.* is there, but B.profileID isn't. — Jacob Barnes, Nov 13 '17 at 20:23
Since those UserIDs are already existing in table A I think you need to perform an UPDATE statement rather than INSERT statement. Or you can delete those records with empty profileID and then insert the new values. Also, when you are inserting, make sure that you have all your columns mentioned in your insert select clause. Otherwise, it will insert default values in whatever the columns that you didn't specify in your select clause — Deepak Janyavula, Nov 13 '17 at 20:26
Possible duplicate of [How do I UPDATE from a SELECT in SQL Server?](https://stackoverflow.com/questions/2334712/how-do-i-update-from-a-select-in-sql-server) — Jacob Barnes, Nov 13 '17 at 20:28

score 2 · Answer 1 · answered Nov 13 '17 at 20:36

2

You should not use 'INSERT' statement for updating a table. Instead use 'UPDATE'.

UPDATE tableA SET tableA.profileID = tableB.profileID FROM tableB b
INNER JOIN TableA a ON a.userID = b.userID WHERE
a.profileID = '' OR a.profileID IS NULL;

answered Nov 13 '17 at 20:36

nish

1,201
6
8

score 0 · Answer 2 · answered Nov 13 '17 at 20:27

You need to update your table (TableA) using UPDATE statement not INSERT INTO :

UPDATE TableA
SET  TableA.profileID = TableB.profileID 
FROM TableB
     INNER JOIN TableA ON TableA.userID = TableB.userID 
WHERE TableA.profileID = '' OR TableA.profileID IS NULL

insert data to column based on query

2 Answers2