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 |
Answers:
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) -- 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)
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0