SQLite Forum

Switching data between two columns
REPLACE is "syntactic sugar" for INSERT or REPLACE where "or replace" is a conflict resolution method.  In order for the conflict resolution method to be invoked there must be a conflict -- and you do not have one.

You can "have a conflict that needs to be resolved" by having a primary key:

SQLite version 3.34.0 2020-09-08 00:37:32
Enter ".help" for usage hints.
sqlite> .mode column
sqlite> .headers on
sqlite> drop table if exists xx;
sqlite> create temp table xx (id integer primary key, c3,c4);
sqlite> insert into xx (c3, c4) values (1,2),(3,4);
sqlite> select * from xx;
id  c3  c4
--  --  --
1   1   2
2   3   4
sqlite> replace into xx select id, c4, c3 from xx where c3 in (1,3);
sqlite> select * from xx;
id  c3  c4
--  --  --
1   2   1
2   4   3

The disadvantage of using "REPLACE" rather than simply updating the table is that if there are any insert or delete triggers then they will fire, whereas if you use update then only update triggers will fire.

This is especially problematic if you have any referential integrity defined against the table xx in which case a "replace" will "update" the table but will delete all the children (if they use CASCADE) or do nothing at all or abort with an error (if the foreign key reference is other than on delete cascade).