How to copy the content of a row to multiple rows on the same table in MySQL

I want to copy a value of a row based on ID and then paste it to other column base on their IDs.

For example, I want to update blocks with ID of 1, 2 to the value of blocks with the ID of 4.
So basically I want to copy the content of ID 4 (or any other ID) and paste it to blocks with ID of 1,2 (or any other IDs) .

For example, I have a table called market and I want to copy the columns fruits and ripeness with ID of 4 to the same table and column with the ID of 1 and 2.

ID | fruits | ripeness|
1  | tomato | very    |
2  | apple  | little  |
3  | orange | very    |
4  | kiwi   | ripe    |


ID | fruits | ripeness|
1  | kiwi   | ripe    |
2  | kiwi   | ripe    |
3  | orange | very    |
4  | kiwi   | ripe    |


Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

A table can join with itself with two different aliases

UPDATE market f
CROSS JOIN market t
SET t.fruits = f.fruits, t.ripeness = f.ripeness
WHERE f.ID = 4
AND t.ID IN ( 1, 2 )

f for rows with ID 4, and t for rows with ID 1, 2.

Method 2

It is said bad habit, that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (almost 30 years ago) and its use is discouraged. If you really want to combine each row from both views, use the proper CROSS JOIN! you just simply do it as per the following.

declare v_fruits varchar(50)
declare v_ripness varchar(50)

 select fruits, ripness into v_fruits, v_ripness from market
 where Id=4

update market set fruits=v_fruits,ripness=v_ripness
where Id in (1,2)

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Notify of

Inline Feedbacks
View all comments
Would love your thoughts, please comment.x