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

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;

CodePudding user response：

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;

CodePudding user response：

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.