How to copy data from one row to another

Question

Possible duplicate:
Update row with data from another row in the same table

I created the table as

create table myTable (id INT, myData varchar(20));

And I have meanings like

insert into myTable VALUES 
(1, 'Value 1'),
(2, 'Value 2'),
(3, 'Value 3');

Now I insert the line as

insert into myTable (id) values (4);

Now I want to insert the data for id 4. The value for id 4 is the same as id 3. SO I think I need to use the UPDATE statement.

I tried with below, however this will not work.

 update myTable SET myData=(select myData FROM myTable WHERE id=3) WHERE id=4;

Please let me know what needs to be done.

Demo in sqlfiddle

Actually, I have myData type as MEDIUMBLOB , however, for demo purpose, I used varchar.

+5

user1203613 Sep 24 '12 at 11:48

2 answers

inner join :

update myTable left_table 
inner join myTable right_table ON right_table.id = 3
set left_table.myData = right_table.myData
where left_table.id = 4;

+2

juergen d · Accepted Answer · 2012-09-24T11:51:56+0000

In MySQL, you cannot update the same table that you select. This results in an error.

"myTable" FROM

MySQL

update myTable
SET myData=(select * from (select myData FROM myTable WHERE id=3) x)
WHERE id=4;