How to map specific column cells to other specific column cells in same table?

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.

Example:

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;

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

You can do a self join with the mapping of the columns in the ON clause:

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.

Method 2

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;

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