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?

score 1 · Answer 1 · answered Dec 27 '16 at 12:47

1

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;

answered Dec 27 '16 at 12:47

valepert

96
5

Wait, I'm not really a pro on this, what should I write after "to" and "from" in the first two lines? – Lara M. Dec 27 '16 at 13:05
"to" and "from" are different alias for the same table, but "to" is the table that contains the record **to** update, "from" is the table **from** you want to extract information – valepert Dec 27 '16 at 13:40

score 0 · Answer 2 · answered Dec 27 '16 at 10:27

0

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'

:)

answered Dec 27 '16 at 10:27

Abdou Rayes

430
4
18

Mysql: set some value from an element

2 Answers2