SQLite Forum

Switching data between two columns
Login

Switching data between two columns

(1) By jose isaias cabrera (jicman) on 2020-09-08 16:55:22 [link] [source]

Greetings!

I messed up! Nothing new in my life. :-) So, I inserted the wrong data in two columns. I know how to fix the problem with a small program, but I was wondering if there was a quick SQL call that I could do to fix the problem. The real problem is that I have a table called t and I have inserted the data that should have gone to column c3 on c4. And I also inserted the data that should have gone to c4 on c3. The desire is to exchange the data found on c3->c4 and the data found on c4->c3. There are two constraints: ProjID and InsertDate. The table definition is thus,

CREATE TABLE t
    (
      ProjID,
      c1,
      c2,
      c3,
      c4,
      c5,
      InsertDate,
      PRIMARY KEY (ProjID,InsertDate)
    );
Thoughts? thanks.

josé

(2) By Richard Hipp (drh) on 2020-09-08 17:02:01 in reply to 1 [link] [source]

Just do:

   UPDATE t SET c3=c4, c4=c3;

The replacement values are computed before any replace occurs, so it is safe to swap values this way.

(4) By jose isaias cabrera (jicman) on 2020-09-08 17:15:00 in reply to 2 [link] [source]

Man, and I was going to do a whole python script to do this. You guys are the best. Thanks Dr. Hipp and David.

josé

(5) By anonymous on 2020-09-08 17:27:45 in reply to 2 [link] [source]

Would this be a viable option when the table contains a lot of data?

alter table t rename c3 to cx;alter table t rename c4 to c3; alter table t rename cx to c4;

Is there a shorter (without using the intermediate name cx) syntax?

(6) By David Raymond (dvdraymond) on 2020-09-08 17:52:12 in reply to 5 [link] [source]

Can depend on how all the code is written for the inserts and for selects from the table. You're effectively re-ordering the columns. So if every single bit of inserting code explicitly calls out the field names: "insert into t (ProjID, c1, c2, c3, c4, c5, InsertDate) values (?,?,?,?,?,?,?);" then it might be ok. If anywhere uses "insert into t values (?,?,?,?,?,?,?);" without the column names, then you've just messed things up again or need to go through every last bit of code again. Plus checking for anywhere you did "select * from" and just assumed "I designed the table, c3 is the 4th thing and c4 is the 5th and it will stay that way." because all those assumptions would now be wrong.

(7) By Keith Medcalf (kmedcalf) on 2020-09-08 18:40:05 in reply to 6 [link] [source]

Not to mention of course that any indexes, triggers, views, etc. Will "follow the data" not "follow the name" -- unless of course you set old fashioned mode first. (to obtain legacy alter table operation).

(8) By jose isaias cabrera (jicman) on 2020-09-08 19:09:25 in reply to 2 [link] [source]

This works perfectly. But I was thinking, and I am asking just to understand the engine behind the process, this statement,

   UPDATE t SET c3=c4, c4=c3;
uses temporary storage, correct? Because if the SQL goes from left to right, c3 will be over-written before it passes the old value to c4. So, something like this is happening in the background:
some-internal-temp = c3;
c3 = c4;
c4 = some-c-temp;
Just a newbie lack of knowledge question. Thanks.

josé

(9.3) By Keith Medcalf (kmedcalf) on 2020-09-08 19:30:21 edited from 9.2 in reply to 8 [source]

No.

When you perform an UPDATE you are really retrieving an "old" payload and then generating a "new" payload to replace it and writing the "new" payload.

So think of it as follows:

struct record { ... };
record old;
record new;
while (there is more to do):
 read(old);
 new = old;
 new.c3 = old.c4;
 new.c4 = old.c3;
 write(new);

References on the LHS of the = in SET refer to the new payload, and references on the RHS refer to the old payload. Thus within an UPDATE trigger you have the "magical" tables OLD and NEW representing the OLD and NEW values respectively.

An INSERT is "half an update" there are no OLD values, only NEW values, hence only the NEW "magical" table is available to insert triggers.

Similarly a DELETE is "half an update" in that there is no NEW values, only OLD values, hence only the OLD "magical" table is available inside delete triggers.

(10) By jose isaias cabrera (jicman) on 2020-09-08 19:32:58 in reply to 9.2 [link] [source]

Thanks, Keith. Perfectly explained.

josé

(11) By Gunter Hick (gunter_hick) on 2020-09-09 07:49:43 in reply to 8 [link] [source]

Think of SQLite behaving as if it were doing

REPLACE INTO <table> (... c3, c4, ...) SELECT ... c4, c3, ... FROM <table>;

i.e. a new record is built using the values from the old record plus any replacements from the UPDATE list before the new record overwrites the old record.

(12) By jose isaias cabrera (jicman) on 2020-09-09 11:55:02 in reply to 11 [link] [source]

Thanks, Gunter. Another fine response.

josé

(13) By anonymous on 2020-09-09 21:09:26 in reply to 11 [link] [source]

This line 

REPLACE INTO <table> (... c3, c4, ...) SELECT ... c4, c3, ... FROM <table>;

is executable after replacing <table> with an appropriate name BUT the records are appended rather than replaced. IS REPLACE a synonym for INSERT?

Also, if you had a where clause, where would it go?

My Session:

sqlite> .mode column
sqlite> .headers on
sqlite> drop table if exists xx;
sqlite> create temp table xx (c3,c4);
sqlite> insert into xx values(1,2),(3,4);
sqlite> select * from xx;
c3  c4
--  --
1   2
3   4
sqlite> replace into xx (c4,c3) select c3,c4 from xx where c3 in(1,3);
sqlite> select * from xx;
c3  c4
--  --
1   2
3   4
2   1
4   3
sqlite>

(14) By Keith Medcalf (kmedcalf) on 2020-09-09 22:11:28 in reply to 13 [link] [source]

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
sqlite>

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).

(15) By Gunter Hick (gunter_hick) on 2020-09-10 06:08:28 in reply to 13 [link] [source]

The analogy given assumes that
1) there is a primary key in the table
2) the affected columns are not in the primary key
both of which are fulfilled by the OP's schema.

Also note the non-optional ellipses which represent the remaining fields of the table and should not be empty. It was not intended to be executed instead of an UPDATE, just as a mental model.

This works for your schema, however:

asql> select rowid,* from xx;
rowid       c3  c4
----------  --  --
1           1   2
2           3   4
asql> replace into xx (rowid,c3,c4) select rowid,c4,c3 from xx;
rows inserted
-------------
2
asql> select rowid,* from xx;
rowid       c3  c4
----------  --  --
1           2   1
2           4   3

(3) By David Raymond (dvdraymond) on 2020-09-08 17:02:17 in reply to 1 [link] [source]

You should be able to switch them with something as simple as

UPDATE t SET c4 = c3, c3 = c4;

And if needed add in constraints on ProjID or InsertDate to a WHERE clause.