I’m trying to see if I can replace contents of a set of cells in a column with a set of cells in a column from other location in the same table.
Book ------- id payload 1 a 2 b 3 c 4 d 5 e
I want a query that will make it the following
Book ------- id payload 1 c 2 e 3 c 4 d 5 e
Say I want payload of 3,5 to replace contents of 1,2.
Here is my attempt:
update ( select id, payload from Books where id in (1,2) ) as normal_book_1 join ( select id, payload from Books where id in (3,5) ) as normal_book_2 set normal_book_1.payload = normal_book_2.payload;
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.
You can do a self join with the mapping of the columns in the
UPDATE Book b1 INNER JOIN Book b2 ON (b1.id, b2.id) IN ((1, 3), (2, 5)) SET b1.payload = b2.payload;
See the demo.
You can execute two
UPDATE statements, as in:
update books set payload = (select payload from books where id = 3) where id = 1 update books set payload = (select payload from books where id = 5) where id = 2;