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)