Update two tables with condition

I have the following scenario
Two Tables
Source Table – Destination Table

Both with the same fields
[ID] [Name] [CPF]

The query must go to the [CPF] columns of the two tables, when the [CPF] of Source and Destination are the same, you must update the [Name] field of the Destination Table as that of Source.
    

Although you have not specified SGBD , query to SQL Server , for example, would look like this:

UPDATE dest
   SET dest.nome = ori.nome
  FROM destino dest
       INNER JOIN origem ori ON ori.cpf = dest.cpf

Some examples of using INNER JOIN are available in Microsoft TechNet: Using internal joins

For Oracle DBMS:

    UPDATE destino dest
       SET dest.nome = NVL((SELECT ori.nome
                              FROM origem ori
                             WHERE ori.cpf = dest.cpf), dest.nome);