Geek Answers Handbook

How to copy a text column value from one row to another in mySQL

I have a pref table with a column value. This value has type text. I want to copy the value of the row value field with identifier 7 to the row value field with identifier 1. Could you help how to do this. I know MS SQL, but I'm new to mySQL.

create table pref ( id int, value text )
+6
sql mysql sql-update
source share
3 answers

In MySQL, you cannot use the subtitle from the same table that you are updating, but you can use the join.

  UPDATE pref AS target LEFT JOIN pref AS source ON source.id = 7 SET target.value = source.value WHERE target.id = 1;
+14
source share
 UPDATE pref SET value = (SELECT value WHERE id = 7) WHERE id = 1
+2
source share

In my case, I tried to copy the encrypted value from one row to 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 this right. Here is my adaptation :)

 name pin john sdhjduwhdowodw7d87e838g83g8g3of... jane //copy from field with value into empty field UPDATE my_table AS target LEFT JOIN my_table AS source ON source.pin != "" SET target.pin = source.pin WHERE target.pin = ""; // Hurray, it works! name pin john sdhjduwhdowodw7d87e838g83g8g3of... jane sdhjduwhdowodw7d87e838g83g8g3of...
0
source share

More articles:


All Articles