mysql in 文本_如何將文本列值從mySQL中的一行復制到另一行

I have table pref having column value. This value has type text. I want copy the value field value of row with id 7 to the value field of row with id 1. Can you please help how to do this. I know MS SQL, but I am new to mySQL.

我有表pref有列值。該值具有類型文本。我想將id為7的行的value字段值復制到id為1的行的value字段。能否幫助您完成此操作。我知道MS SQL,但我是mySQL的新手。

create table pref

(

id int,

value text

)

3 个解决方案

#1

17

In MySQL you can't use a subselect from the same table you are updating, but you can use a join.

在MySQL中,您不能使用要更新的同一個表中的子選擇,但可以使用連接。

UPDATE pref AS target

LEFT JOIN pref AS source ON source.id = 7

SET target.value = source.value

WHERE target.id = 1;

#2

1

UPDATE

pref

SET

value = (SELECT value WHERE id = 7)

WHERE

id = 1

#3

0

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.

就我而言,我試圖將一行中的加密值復制到同一個表中另一行的空字段中。在這種情況下,我需要將約翰的PIN復制到Jane的PIN中。

@mohang has it right. Here is my adaptation :)

@mohang說得對。這是我的改編:)

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...