how to copy one row data to another

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;

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;

Updated fiddle

Related