Mysql: set some value from an element

Question

I my db I have some values with multiple Id and an Id with whom I reconciled them, for instance:

Id: 234 | Viaf_id: 3475903 | Name: Arturo Rossi | First name: Rossi | Last name: Arturo

Id: 345 | Viaf_id: 3475903 | Name: Rossi, Arturo | First name: Arturo | Last name: Rossi

I would like to set all those value with the viaf_id 3475903 with the name of 345. Now I do it "manually" with:

UPDATE viaf_reconcile 
SET id_creator="345", 
    Name="Rossi, Arturo",  
    FirstName="Arturo", 
    LastName="Rossi"  
WHERE `id_viaf` = '3475903'

But this takes me a lot of times, is there a way to set the values "Name", "FirstName" and "LastName" with those of a specific id_creator where there is a specific id_viaf?

Answer 1

i think if you don't specify the "Name" , "FirstName", "LastName" fields , mysql will set the old ones so , i think this query will do the work :

UPDATE viaf_reconcile 
SET id_creator="345"  
WHERE `id_viaf` = '3475903'

:)

Answer 2

You can JOIN during UPDATE doing something like:

  UPDATE viaf_reconcile to
  JOIN viaf_reconcile from
  ON from.id_viaf = to.id_viaf
  AND to.id_viaf = '3475903'
  SET to.Name = from.Name;