mysql - MS Access Update on specific SQL select same table -

i'm trying update existing database (removing duplicates). can see structure follows :

i have database on specific entries marked "main". these entries need updated data duplicate records, having same name.

(updated table reflect question better)

it this:

+----+------+-----------------+--------------+---------+ | id | name | field-to-update |  duplication |  source | +----+------+-----------------+--------------+---------+ | .  |    |  xxx            |  main        |       1 | | .  |    |  yyy            |  ""          |       2 | | .  |    |  zzz            |  ""          |       3 | | .  |  b   |  foo            |  ""          |       1 | | .  |  b   |  bar            |  main        |       2 | +----+------+-----------------+--------------+---------+ 

should result in

+----+------+-----------------+--------------+-----------------------------------------------+ | id | name | field-to-update |  duplication |                     source                    | +----+------+-----------------+--------------+-----------------------------------------------+ | .  |    |  yyy            |  main        |  1                                            | | .  |    |  yyy            |  ""          |  2                                            | | .  |    |  zzz            |  ""          |  3 (should updated specific source) | | .  |  b   |  bar            |  ""          |  1                                            | | .  |  b   |  bar            |  main        |  2 (should updated specific source) | +----+------+-----------------+--------------+-----------------------------------------------+ 

do of have idea how tackle this? i've tried multiple queries couple of days without success.

you use update join

update  t set t.field_to_update = x.field_to_update your_table t inner join  ( select name, field_to_update                           your_table                           duplication <> 'main') x   ) on t.name = x.name  t.duplication = 'main'