How to copy text column value from one row to another row in mySQL
In MySQL you can't use a subselect from the same table you are updating, but you can use a join.
UPDATE pref AS targetLEFT JOIN pref AS source ON source.id = 7 SET target.value = source.value WHERE target.id = 1;
In my case, I was trying to copy an encrypted value from one row into an empty field in another row in the same table. In this case, I needed to copy john's PIN into Jane's PIN.
@mohang has it right. Here is my adaptation :)
name pinjohn sdhjduwhdowodw7d87e838g83g8g3of...jane //copy from field with value into empty fieldUPDATE my_table AS targetLEFT JOIN my_table AS source ON source.pin != ""SET target.pin = source.pinWHERE target.pin = ""; // Hurray, it works!name pinjohn sdhjduwhdowodw7d87e838g83g8g3of...jane sdhjduwhdowodw7d87e838g83g8g3of...