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

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    |

To:

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

CodePudding user response：

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.

CodePudding user response：

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)

 -- SQLINES LICENSE FOR EVALUATION USE ONLY
 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)