select - MySQL update with a subquery -

i trying update specific row in table based on value in table, can't seem figure out how it:

update users set a.val = (select value users userid = 4) a.userid = 1

but getting error

lookup error - mysql database error: can't specify target table 'a' update in clause

any ideas missing here?

use join syntax , non-equi join

update users join users b     on a.userid = 1    , b.userid = 4    set a.value = b.value

here sqlfiddle demo

Detect