How to update records along with select from another table?

Question

MySQL

Admin table with three columns AdminID, username, password. Table Users with three of the same columns. How to make a query that would update the password in the Admin table with the password from the Users table, where UserID=123?

Answer 1

MySQL:

UPDATE Admins A
JOIN Users U ON U.UserID = A.AdminID
SET A.password = U.password
WHERE U.UserID = 123;

Test MySQL here
MS SQL:

UPDATE Admins
SET Admins.password = U.password
FROM Admins A JOIN Users U ON U.UserID = A.AdminID
WHERE A.AdminID = 123;

MS SQL Fiddle