how to get data from one table to another based on one common column in sql-CodePudding

there are two tables named test1 and test2

test1

Name     ID Refnum
CHANDRA  1   1234
Vinay    2   1324

test2

Username Userid  Usernum
A         10      1322
B         12      1221
                  1234
                  1324

when Refnum and Usernum are equal we have to update test2 table username and user id with data from test1 name and id respectively. need a query for this.

CodePudding user response：

something like this?

UPDATE test2
SET Username = name and Userid = ID
FROM test1, test2
WHERE test1.Refnum = test2.Usernum

CodePudding user response：

Answer given by Shreyas Prakash is correct but there's a syntax error in query. Try below query

UPDATE test2
SET Username = name, Userid = ID
FROM test1, test2
WHERE test1.Refnum = test2.Usernum