SQL SELECT followed by UPDATE

Question

I have Employee table and HR table. Address of employee ( address column ) is there in both the tables. I want to update the HR table's address column with the address from Employee table, for all those records wherever there is a mismatch between the HR table's address and Employee table's address for the same employee.employee-id is primary key in employee table and foreign key in HR table.

Answer 1

You can still do JOIN on UPDATE statements.

UPDATE  HR a
        INNER JOIN Employee b
            ON a.EmpID = b.ID  -- relationship column
SET     a.address = b.address
WHERE   a.address <> b.address -- to make this query faster
                               -- filter only those address that didn't match