how to copy one row data to another

Question

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

I have created table as

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

And I have values as

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

Now I insert row as

insert into myTable (id) values (4);

Now I want to insert data for id 4. Value for id 4 is same as id 3. SO I believe I need to use UPDATE statement.

I tried with below, however it won't work.

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

Please let me know what need to be done.

Demo at sqlfiddle

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

juergen d · Accepted Answer · 2019-03-22T05:19:05.400

10

In MySQL you can't update the same table your are selecting from. That leads to the error

You can't specify target table 'myTable' for update in FROM clause

But you can trick MySQL by creating a temp table

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

edited Mar 22 '19 at 05:19

answered Sep 24 '12 at 11:51

juergen d

1

that's such a nice and neat answer! btw, what does the "x" in the end of the 2nd line mean? – evilReiko Dec 16 '13 at 10:37
2

The `x` is an alias name for the inner subquery. You need to give subqueries a name. – juergen d Dec 16 '13 at 17:49

score 2 · Answer 2 · answered Sep 24 '12 at 12:00

2

Can you not just use an inner join with aliased tables like this:

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;

answered Sep 24 '12 at 12:00

Paul Aldred-Bann