Please, use this:
UPDATE t1
SET t1.address1 = t2.address1
FROM TableName1 AS t1
INNER JOIN TableName2 AS t2 ON t1.name_id = t2.name_id
WHERE t1.account_no = '938'
Do you see changes?
In other words, above means:
UPDATE
MainTable
SET
MainTable.col1 = OtherTable.col1,
MainTable.col2 = OtherTable.col2
FROM
MainTable
INNER JOIN
OtherTable
ON
MainTable.id = OtherTable.id
Source:
http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server[
^]
[EDIT]
UPDATE PEOPLE a
SET a.SURNAME = (
select b.SURNAME
from PEOPLE b
where b.NI.NO = a.NI_NUMBER
)
Source:
ORA-00933: SQL command not properly ended[
^]
[/EDIT]