Write SQL statement to look up row by join id, and insert a value from the joined row into the paren-CodePudding

I have a table called Form that contains a User_id that links to the User table.

I want to pull out the username from the user table, and insert it into a new field, Form.ownersUsername. I don't want to manually generate the insert statements myself, but get it to loop through all the fields and set the value Form.ownersUsername equal to the value of User.username.

I know how to join & select the tables so my I have all the relevant information selected using the below statement, but not sure on my next step.

SELECT * FROM Form
LEFT JOIN User U ON Form.id = U.id;

Thanks

Form table
-----------------------------------------------------
| Form_id |  title  |  Owner_id  |  Owner_username  |
|    1    |    x    |      1     |      <null>      | 
|    2    |    y    |      1     |      <null>      | 
 ----------------------------------------------------

User table
------------------------
| User_id |  username  |
|    1    |   000001   |
|    2    |   000002   |
------------------------

For reference: I have to do this as my system creates a new user entry if the user details changes (they get promoted for example) as we need to know the details of the user at a point in time however when searching, we want to search by force number, not user entry.

CodePudding user response：

It sounds like you just need to update the rows that have a matching user Id

update f set
  f.Owner_username=u.username
from Form f
join [User] u ON = u.id = f.id;

This should probably be an inner join unless you wish to update all non-matching rows as NULL also.

CodePudding user response：

You can use select statement for define value you want to insert. Here's an example:

INSERT INTO Form (Owner_id, Owner_username)
SELECT
    User_id,
    username
FROM User
WHERE id = 2

This query will insert to columns Owner_id and Owner_username based on select result