Copy blob from one row to another

Question

I have a corrupt blob in a row.
I'd like to replace that with another blob from the same table.

How can I do that with sql statements only? (No file system access if at all possible.)

Answer 1

Poor man's change:

mysql> CREATE TABLE `foo` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `content` blob,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> INSERT INTO foo VALUES (NULL, 'bar'), (NULL, 'another_bar');

mysql> SELECT * FROM foo;
+----+-------------+
| id | content     |
+----+-------------+
|  1 | bar         |
|  2 | another_bar |
+----+-------------+
2 rows in set

mysql> SELECT content INTO @newContent FROM foo WHERE id=2;

mysql> UPDATE foo SET content=@newContent WHERE id=1;

mysql> SELECT * FROM foo;
+----+-------------+
| id | content     |
+----+-------------+
|  1 | another_bar |
|  2 | another_bar |
+----+-------------+
2 rows in set

And now I've done it without using session variables:

 UPDATE foo, (SELECT content AS selectContent FROM foo WHERE id=2)selectFoo 
  SET content=selectContent 
  WHERE id=1;