How can I use a result of select in update query? For example:
Update access_rights
set rfidcode=(Select rfidcode from users where name like 'thomas')
where id_access_rights=3;
This doesn't work. Can anyone help me ?
-
1This question is asked fairly often. 3rd time today?Strawberry– Strawberry2015-08-10 16:47:51 +00:00Commented Aug 10, 2015 at 16:47
Add a comment
|
2 Answers
assuming you want to update a single record your select query needs to return a single result. use the limit keyword.
Update access_rights
set rfidcode=(Select rfidcode from users where name like 'thomas' limit 1)
where id_access_rights=3;
vkp solution if you want to update many related records but you have to watch your joins or you'll get errors, or worse corrupt your data
1 Comment
iris2015
Yes I did it like this :) Thank you too
Update access_rights a, users u
set a.rfidcode = u.rfidcode
where a.userid = u.userid --change this to appropriate join column
and u.name like '%thomas%'
and a.id_access_rights=3;
1 Comment
iris2015
thanks but I did it myself like this update access_rights set users_rfidcode= (select rfidcode from users where nickname like 'kin') where id_access_rights=14;