Simple example of update using nested query or in other words embedded select to self. The context of the query is:
there is a table coin with columns name and symbol. Some symbols are broken in this table and the idea is to recover missing symbols by name. The update is done as self referecing query:

UPDATE coin c1, 
(
SELECT distinct name as name, symbol AS maxSymbol
FROM coin
WHERE symbol > '0'
) t
SET c1.symbol = t.maxSymbol
WHERE c1.symbol = '0'
-- AND c1.name = 'Bitcoin' 
AND t.name = c1.name

result:

before:

  • name: Bitcoin, symbol BTC
  • name: Bitcoin, symbol 0
  • name: Riple, symbol XRP
  • name: Riple, symbol 0

after:

  • name: Bitcoin, symbol BTC
  • name: Bitcoin, symbol BTC
  • name: Riple, symbol XRP
  • name: Riple, symbol XRP