CODEWITHSUNDEEP
mysqlsql
iris2015
iris2015

Reputation: 5

Set a value of select in update query MYSQL

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 ?

Upvotes: 1

Views: 42

Answers (2)

djack109
djack109

Reputation: 1377

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

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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;

Upvotes: 1

Related Questions